|
|
Posted by Jeff North on 06/03/06 04:29
I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00
and expriencing problems with setting referential integrity on a link
table. The tables' schema is as follows:
-------------------------------------------------------------------
CREATE TABLE competencies (
CID bigint identity(1,1) CONSTRAINT pk_CID PRIMARY KEY,
LockedBy bigint DEFAULT 0 NOT NULL
CONSTRAINT fk_UserID
REFERENCES usr_info(userID)
ON DELETE SET DEFAULT
ON UPDATE CASCADE
)
---------------------------------------------------------
CREATE TABLE usr_info (
userID bigint IDENTITY(0,1) CONSTRAINT pk_UID PRIMARY KEY,
ActiveFlag bit default 0 NOT NULL, --(1='Yes', 0='No')
FirstName varchar(100) default '' NOT NULL,
LastName varchar(100) default '' NOT NULL
)
-------------------------------------------------------
CREATE TABLE competency_hdr (
fkCID bigint default 0 NOT NULL
CONSTRAINT fkCID_ch
REFERENCES competencies(CID)
ON DELETE CASCADE
ON UPDATE CASCADE,
ApprovedBy bigint default 0 NOT NULL
CONSTRAINT fkUserID_ch
REFERENCES usr_info(userID)
ON DELETE SET DEFAULT -- NO delete if user is deleted
ON UPDATE CASCADE
)
--------------------------------------------------------
When I execute the above I get the following error message.
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'fkUserID_ch' on table
'competency_hdr' may cause cycles or multiple cascade paths. Specify
ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Now, if i swap the fields around then the error message changes to
that of the fkCID field.
Basically what I want is:
when I delete a competency record I need all references to this record
to be deleted.
when I delete a user I want to set the foreign key to zero (the record
must remain on the database).
Obviously there is something I'm missing here. Any advice, anyone?
---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Navigation:
[Reply to this message]
|