"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", one for the user who types
and one for the user who aproves.
Note the "APROVED_BY" foreign key can be null because when a request is
saved the user who aproves doesn't exist yet.
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".
Could someone tell me why SQL Server returns this error?
Thanks in advance
RegardsCREATE TABLE USERS (
[LOGIN] [varchar] (10) NOT NULL ,
[NAME] [varchar] (20) NOT NULL
)
ALTER TABLE USERS ADD
CONSTRAINT [PK__USERS__07DE5BCC] PRIMARY KEY (
[LOGIN]
) ON [PRIMARY]
GO
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
The above one is working, you can only have one Cascade to one Table.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi Jens, thanks for your reply.
Does it mean that I'm not able to have "APROVED_BY" foreign key in table
REQUESTS?
"Jens" escribió:

> CREATE TABLE USERS (
> [LOGIN] [varchar] (10) NOT NULL ,
> [NAME] [varchar] (20) NOT NULL
> )
> ALTER TABLE USERS ADD
> CONSTRAINT [PK__USERS__07DE5BCC] PRIMARY KEY (
> [LOGIN]
> ) ON [PRIMARY]
> GO
> 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 CASCA
DE
>
> The above one is working, you can only have one Cascade to one Table.
>
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||<DIV>"Rolandpish" <Rolandpish@.discussions.microsoft.com>
wrote in message
news:9B951B40-585A-444A-A125-2A87FF0FB748@.microsoft.com...</DIV>> Hi Jens,
thanks for your reply.
> Does it mean that I'm not able to have "APROVED_BY" foreign key in table
> REQUESTS?
>
No it means you can only have one cascading foreign key. However that's
fine since you should have ZERO cascading foreign key. ON UPDATE CASCADE
should be used very rarely, and probably not here. It is only used for
cascading updates the the primary key, which you generally shouldn't be
doing.
Here's all you need:
CREATE TABLE USERS
(
[LOGIN] [varchar] (10) NOT NULL PRIMARY KEY ,
[NAME] [varchar] (20) NOT NULL
)
CREATE TABLE REQUESTS
(
[ID] [numeric](5, 0) NOT NULL PRIMARY KEY,
[DATE] [datetime] NOT NULL ,
[NOTES] [varchar] (100) NOT NULL ,
[TYPED_BY] [varchar] (10) NOT NULL REFERENCES USERS,
[APROVED_BY] [varchar] (10) NULL REFERENCES USERS
)
David