Question about relationships - "cycles or multiple cascade paths" error

I have 2 tables:
Table: UserTable
Field: Id (pk)
Field: FullName
Table: MarriageTable
Field: MarriageId (pk)
Field: Husband
Field: Wife
The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
valid users from the UserTable.
In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
MarriageTable.Wife. But, the Diagram has trouble saving the Wife
relationship. Any ideas? Am I doing this correctly?
Here's the error:
'UserTable' table saved successfully
'MarriageTable' table
- Unable to create relationship 'FK_MarriageTable_UserTable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_MarriageTable_UserTable1' on table
'MarriageTable' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
Thanks.
On Wed, 29 Dec 2004 10:01:20 -0500, VB Programmer wrote:

>I have 2 tables:
>Table: UserTable
>Field: Id (pk)
>Field: FullName
>Table: MarriageTable
>Field: MarriageId (pk)
>Field: Husband
>Field: Wife
>The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
>valid users from the UserTable.
>In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
>UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
>MarriageTable.Wife. But, the Diagram has trouble saving the Wife
>relationship. Any ideas? Am I doing this correctly?
Hi VB Programmer,
Simple point and click interfaces can be great if you don't know the nitty
gritty details and don't want to know them. However, when dealing with a
complex DBMS such as SQL Server, it's best to do know the nitty gritty
details. You should get used to defining all your tables and constraints
in pure SQL.
CREATE TABLE Users (UserID int NOT NULL,
FullName varchar(60) NOT NULL,
PRIMARY KEY (UserID)
)
CREATE TABLE Marriages (MarriageID int NOT NULL,
Husband int NOT NULL,
Wife int NOT NULL,
PRIMARY KEY (MarriageID),
UNIQUE (Husband, Wife),
FOREIGN KEY (Husband)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Wife)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE
)
Run this in Query Analyzer and you'll be 100% sure that you didn't
accidentally create a foreign key "in the wrong direction".
Unfortunately, you'll still get an error. This is a limitation in SQL
Server 2000. If you use the cascades option on foreign key constraints,
SQL Server demands that there never be more than one cascading path from
any table to any other table. You'll have to modify at least one of your
foreign key constraints to do ON DELETE NO ACTION ON and ON UPDATE NO
ACTION (the default options). You'll also have to use another solution if
your applications requires cascading behaviour. Possible solutions are to
encapsulate all logic in a stored procedure or to use a trigger.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)