I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
************************************************** ********
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
Jamie
Jamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> ************************************************** ********
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie
|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
************************************************** ********[vbcol=seagreen]
CLUSTERED[vbcol=seagreen]
DealAttribute[vbcol=seagreen]
key
>
>.
>
|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> problem
> the table with two
> DELETE CASCADE ON
> message
> ************************************************** ********
> CLUSTERED
> DealAttribute
> key
|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
to[vbcol=seagreen]
ON[vbcol=seagreen]
error[vbcol=seagreen]
UPDATE[vbcol=seagreen]
************************************************** ********[vbcol=seagreen]
Deal[vbcol=seagreen]
Delete/Update[vbcol=seagreen]
the[vbcol=seagreen]
SQL I
>
>.
>
|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label receive. Show all posts
Showing posts with label receive. Show all posts
Thursday, March 8, 2012
Cascade Deletes
I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Cascade Deletes
I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
****************************************
******************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE O
N
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx
.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> ****************************************
******************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
> )
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
> )
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2670701c4628c$11b4edb0$a401280a@.phx
.gbl...
****************************************
******************[vbcol=seagreen]
CLUSTERED[vbcol=seagreen]
DealAttribute[vbcol=seagreen]
key[vbcol=seagreen]
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> problem
> the table with two
> DELETE CASCADE ON
> message
> ****************************************
******************
> CLUSTERED
> DealAttribute
> key|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
> )
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:267be01c46290$e4e593c0$a501280a@.phx
.gbl...
to[vbcol=seagreen]
ON[vbcol=seagreen]
error[vbcol=seagreen]
UPDATE[vbcol=seagreen]
****************************************
******************[vbcol=seagreen]
Deal[vbcol=seagreen]
Delete/Update[vbcol=seagreen]
the[vbcol=seagreen]
SQL I[vbcol=seagreen]
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, whe
n I realised I was writting utter rubbish. Check to see that you SQL Server
is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK wer
e created without problem. I also poped a couple of rows in the table, and t
he cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
****************************************
******************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE O
N
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx
.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> ****************************************
******************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
> )
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
> )
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2670701c4628c$11b4edb0$a401280a@.phx
.gbl...
****************************************
******************[vbcol=seagreen]
CLUSTERED[vbcol=seagreen]
DealAttribute[vbcol=seagreen]
key[vbcol=seagreen]
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> problem
> the table with two
> DELETE CASCADE ON
> message
> ****************************************
******************
> CLUSTERED
> DealAttribute
> key|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
> )
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:267be01c46290$e4e593c0$a501280a@.phx
.gbl...
to[vbcol=seagreen]
ON[vbcol=seagreen]
error[vbcol=seagreen]
UPDATE[vbcol=seagreen]
****************************************
******************[vbcol=seagreen]
Deal[vbcol=seagreen]
Delete/Update[vbcol=seagreen]
the[vbcol=seagreen]
SQL I[vbcol=seagreen]
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, whe
n I realised I was writting utter rubbish. Check to see that you SQL Server
is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK wer
e created without problem. I also poped a couple of rows in the table, and t
he cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Saturday, February 25, 2012
Capturing XML
Ok, this is a broad question. Our system is going to receive xml files from vendors that contain background check results for individuals. We want to capture the xml results in the db for historical purposes and to have a copy of what the vendor sent before we translated the results and updated our system. I was going to store the xml in a table with a text column but wasn't sure if there was a better way to do this. Anyone have any suggestions?
ThanksI think there have a couple of posts recently on this very subject. I think the general consensus is to store the XML in an NText or Text column. Individual contributors have cautioned that these column types require additional "attention" in the form of more frequently scheduled DBCC CHECKDB statements. That has not been my experience, but others have suggested it.
Certainly in your case, since you want to keep a copy of the data received prior to manipulating it, storing it "as-is" is probably a good idea. An alternative might be to store the filename (as a pointer) in the database while saving the XML to a file on either the database server or some other location.
Regards,
hmscott|||Well, you're going to need to use the data too, right?
DECLARE @.xml varchar(1000)
SET @.xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
<InsertSessionData>
<Input>
<SessionID>6F9619FF-8B86-D011-B42D-0C04FC964FF</SessionID>
<Last_Accessed>20030101</Last_Accessed>
<State><somexml></somexml></State>
</Input>
</InsertSessionData>'
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT * FROM OPENXML(@.idoc, '/InsertSessionData/Input', 2)
WITH(
SessionID varchar(8000),
Last_Accessed Datetime,
State Text
)|||Brett,
Your point is well taken. However, there are other ways of getting at the data in the documents. We use a VBScript and the MS DOM to strip out the necessary data and store it in the relational tables. We don't keep a copy of the XML docs (no requirement to do so), but if I did, I could simply add a line to insert the text into an archive table for historical purposes.
But I don't use OPENXML to parse the XML itself.
Regards,
hmscott|||Thanks for the ideas. We're going to extract the data we need through java code. The table is strictly for archiving. Kind of an insurance policy if we have problems with our interface application.
ThanksI think there have a couple of posts recently on this very subject. I think the general consensus is to store the XML in an NText or Text column. Individual contributors have cautioned that these column types require additional "attention" in the form of more frequently scheduled DBCC CHECKDB statements. That has not been my experience, but others have suggested it.
Certainly in your case, since you want to keep a copy of the data received prior to manipulating it, storing it "as-is" is probably a good idea. An alternative might be to store the filename (as a pointer) in the database while saving the XML to a file on either the database server or some other location.
Regards,
hmscott|||Well, you're going to need to use the data too, right?
DECLARE @.xml varchar(1000)
SET @.xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
<InsertSessionData>
<Input>
<SessionID>6F9619FF-8B86-D011-B42D-0C04FC964FF</SessionID>
<Last_Accessed>20030101</Last_Accessed>
<State><somexml></somexml></State>
</Input>
</InsertSessionData>'
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT * FROM OPENXML(@.idoc, '/InsertSessionData/Input', 2)
WITH(
SessionID varchar(8000),
Last_Accessed Datetime,
State Text
)|||Brett,
Your point is well taken. However, there are other ways of getting at the data in the documents. We use a VBScript and the MS DOM to strip out the necessary data and store it in the relational tables. We don't keep a copy of the XML docs (no requirement to do so), but if I did, I could simply add a line to insert the text into an archive table for historical purposes.
But I don't use OPENXML to parse the XML itself.
Regards,
hmscott|||Thanks for the ideas. We're going to extract the data we need through java code. The table is strictly for archiving. Kind of an insurance policy if we have problems with our interface application.
Tuesday, February 14, 2012
can't use the index tuning wizard wioth a function??
Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.
Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert
|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.
Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert
|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
can't use the index tuning wizard wioth a function??
Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID =
A.ID
what can I do?
thanks.
Jerome.Jj wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jj wrote:
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
can't use the index tuning wizard wioth a function??
Hi,
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID = A.ID
what can I do?
thanks.
Jerome.Jéjé wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jéjé wrote:
>> Hi,
>> I receive this error when I try to execute the index tuning wizard:
>> "There are no events in the workload. Either the trace
>> file contained no SQL batch or RPC events or the SQL
>> script contained no SQL queries."
>> I have tried from the query analyzer and from a workload trace file,
>> in the 2 cases I receive the error.
>> My query contain a join to a custom function which return a simple
>> list. if I remove the function, then the index tuning works fine.
>> my query:
>> select * from table1 inner join dbo.MyFunction(@.Param) A on
>> table1.ID = A.ID
>> what can I do?
>> thanks.
>> Jerome.
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
I receive this error when I try to execute the index tuning wizard:
"There are no events in the workload. Either the trace
file contained no SQL batch or RPC events or the SQL
script contained no SQL queries."
I have tried from the query analyzer and from a workload trace file, in the
2 cases I receive the error.
My query contain a join to a custom function which return a simple list.
if I remove the function, then the index tuning works fine.
my query:
select * from table1 inner join dbo.MyFunction(@.Param) A on table1.ID = A.ID
what can I do?
thanks.
Jerome.Jéjé wrote:
> Hi,
> I receive this error when I try to execute the index tuning wizard:
> "There are no events in the workload. Either the trace
> file contained no SQL batch or RPC events or the SQL
> script contained no SQL queries."
> I have tried from the query analyzer and from a workload trace file,
> in the 2 cases I receive the error.
> My query contain a join to a custom function which return a simple
> list. if I remove the function, then the index tuning works fine.
> my query:
> select * from table1 inner join dbo.MyFunction(@.Param) A on
> table1.ID = A.ID
> what can I do?
> thanks.
> Jerome.
You probably chose the wrong template for recording of events in profiler.
There is a template SQLProfilerTuning. It should work with that one.
Kind regards
robert|||I'm using standard templates which works fine with any query those with my
function.
but why I can't optimize from query analyzer?
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:e$XXF4wcFHA.2760@.tk2msftngp13.phx.gbl...
> Jéjé wrote:
>> Hi,
>> I receive this error when I try to execute the index tuning wizard:
>> "There are no events in the workload. Either the trace
>> file contained no SQL batch or RPC events or the SQL
>> script contained no SQL queries."
>> I have tried from the query analyzer and from a workload trace file,
>> in the 2 cases I receive the error.
>> My query contain a join to a custom function which return a simple
>> list. if I remove the function, then the index tuning works fine.
>> my query:
>> select * from table1 inner join dbo.MyFunction(@.Param) A on
>> table1.ID = A.ID
>> what can I do?
>> thanks.
>> Jerome.
> You probably chose the wrong template for recording of events in profiler.
> There is a template SQLProfilerTuning. It should work with that one.
> Kind regards
> robert
>
Subscribe to:
Posts (Atom)