cycles or multiple cascade paths

Question

cycles or multiple cascade paths

rami.atedgi

Right there with Babe

Points: 776
More actions
December 4, 2007 at 7:43 am

#119592

Hello everybody,
My query is like this:
CREATE TABLE MFGINSTRUCTIONS
([APPROVEDAT] datetime ,[APPROVEDBY] varchar (15) ,
[CREATED_BY] varchar (15) ,[EXPDATE] datetime ,
[INSTRUCTIONCODE] int ,[INSTRUCTIONSNAME] varchar (100) ,
[MATCODE] varchar (30) ,[ORIGREC] int ,
[ORIGSTS] varchar (1) ,[RETIREDBY] varchar (15) ,
[RETIREDDAT] datetime ,[STARDOC_ID] varchar (20) ,
[STARTDDATE] datetime ,[STATUS] varchar (10) ,
CONSTRAINT MFGINSTRUCTIONS_PK PRIMARY KEY (INSTRUCTIONCODE ),
CONSTRAINT MFGINSTRUCTIONS_FK2 FOREIGN KEY ( APPROVEDBY ) REFERENCES USERS (USRNAM ) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT MFGINSTRUCTIONS_FK3 FOREIGN KEY ( CREATED_BY ) REFERENCES USERS (USRNAM ) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT MFGINSTRUCTIONS_FK1 FOREIGN KEY ( MATCODE ) REFERENCES MATERIALS ( MATCODE ) ON DELETE CASCADE ON UPDATE CASCADE )
I got the following error:
Introducing FOREIGN KEY constraint 'MFGINSTRUCTIONS_FK3' on table 'MFGINSTRUCTIONS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.\rCould not create constraint. See previous errors.
first:what is mean "cycles or multiple cascade paths" ,in which scenario can it happens?
second:the query is built dynamically via code, How can I recognize
this problem before its happens so I can make a condition statement to handel it.
thanking you in advance
Rachamim

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Typically you do not want 2 FKs to one table. I think there's a concern that this will create a loop with tableA referencing TableB, which references TableA for some reason.

Do you need both of those columns referencing the PK of the first table? Is not one sufficient?

noeld SSC Guru Points: 96590 More actions · Answer 2

rami.atedgi (12/4/2007)
Hello everybody,
My query is like this:
CREATE TABLE MFGINSTRUCTIONS
([APPROVEDAT] datetime ,[APPROVEDBY] varchar (15) ,
[CREATED_BY] varchar (15) ,[EXPDATE] datetime ,
[INSTRUCTIONCODE] int ,[INSTRUCTIONSNAME] varchar (100) ,
[MATCODE] varchar (30) ,[ORIGREC] int ,
[ORIGSTS] varchar (1) ,[RETIREDBY] varchar (15) ,
[RETIREDDAT] datetime ,[STARDOC_ID] varchar (20) ,
[STARTDDATE] datetime ,[STATUS] varchar (10) ,
CONSTRAINT MFGINSTRUCTIONS_PK PRIMARY KEY (INSTRUCTIONCODE ),
CONSTRAINT MFGINSTRUCTIONS_FK2 FOREIGN KEY ( APPROVEDBY ) REFERENCES USERS (USRNAM ) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT MFGINSTRUCTIONS_FK3 FOREIGN KEY ( CREATED_BY ) REFERENCES USERS (USRNAM ) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT MFGINSTRUCTIONS_FK1 FOREIGN KEY ( MATCODE ) REFERENCES MATERIALS ( MATCODE ) ON DELETE CASCADE ON UPDATE CASCADE )
I got the following error:
Introducing FOREIGN KEY constraint 'MFGINSTRUCTIONS_FK3' on table 'MFGINSTRUCTIONS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.\rCould not create constraint. See previous errors.
first:what is mean "cycles or multiple cascade paths" ,in which scenario can it happens?
second:the query is built dynamically via code, How can I recognize
this problem before its happens so I can make a condition statement to handel it.
thanking you in advance
Rachamim

Suppose you delete several rows from USRNAM, Which of the cascade path are going to be used either all rows with the "deleted" "APROVEBY" or all rows with the deleted "CREATED_BY" ?

That is why you can not use Multiple cascade paths ...

Cheers,

* Noel

rami.atedgi Right there with Babe Points: 776 More actions · Answer 3

Thank,

Ok now I understand the problem little more after your explanation but

in fact, I just recreate the table,

I got an exist database, include this table, which I want to recreate.

I was analyzed the database and get the metadata, which give me this list of FK's .

so I wonder how this table created in the first time?

Rachamim