Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: 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):

    Code:
    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".

    Code:
    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:
    Code:
    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

    Roland
    Last edited by rolandpish; 09-11-06 at 13:49.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Posts
    21
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •