Friday, February 17, 2012

cycles or multiple cascade paths error

Hi there.
I've been searching for this error specifically but I haven't found anything yet.

I have these two tables (USERS and REQUESTS):

USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)

where LOGIN is the primary key.

The problem comes when I try to create the "REQUESTS" table.
In these requests there's one user who types the request. After one or two days, there's other user who aproves the request. The problem is that I need two foreign keys referencing the table "USERS".

CREATE TABLE REQUESTS (
[ID] [numeric](5, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL ,
[APROVED BY] [varchar] (10) NULL
) ON [PRIMARY]
GO

ALTER TABLE REQUESTS ADD
CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE REQUESTS ADD
CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY
(
[TYPED_BY]
) REFERENCES [USERS] (
[LOGIN]
) ON UPDATE CASCADE ,
CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY
(
[APROVED_BY]
) REFERENCES [USERS] (
[LOGIN]
) ON UPDATE CASCADE

And SQL returns:

Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table 'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

Ok, after that, I tried creating a new table to store aprovals (Table with two fields: "REQUEST_ID" and "APROVED_BY").
So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.
The same error comes up.

I don't think this structure goes into "cycles" or "multiple cascades".

How can I do this?
Thanks in advance
Regards

RolandYou can't have multiple dependency paths for CASCADE UPDATE/DELETE operations. If you want to enforce referential integrity between these tables, one of the relationships will need to be enforced with a trigger.|||Blindman, thanks for your reply.
I was afraid that I had to mess with triggers to solve this.
Oh man, I'll have to do it (I've never done triggers before).

Best Regards|||They aren't very difficult. The code you will need is very simple. Just read the sections in Books Online about the virtual "inserted" and "deleted" tables. The only other thing you need to make sure of is that your trigger works for transactions involving multiple records. Many people make the mistake of assuming the only one record will be inserted, deleted, or updated at a time, and this is what leads to problems.
If you run into anything you don't understand, post another thread on this forum.

No comments:

Post a Comment