Referential Integrity problem

SQL Apprentice Question
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

Celko Answers
Have you considered a relational design? It would have actual keys of
a proper data type and some research about standards? Do you really
know anyone with a first_name that is VARCHAR(100)? Why did you think
that IDENTITY can ever, ever be a key? And a key with a DEFAULT?
Defaults are for attributes which can have multiple occurrences of
values. And why did think that you need a BIGINT so you can have more
users than the entire population of Earth?

Can you use the "Dictionary of Occupational Titles" for
Competencies? What research did you do?


Why is a user an attribute of a Competency? Only Fred can be a
carpenter? Why do you use bit flags in SQL?


CREATE TABLE Competencies
(dot_code CHAR(6) NOT NULL PRIMARY KEY,
dot_description VARCHAR(25) NOT NULL);


Shouldn't there be a user attribute for approval powers? You did not
show one.


CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY, -- needs research!
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL);



>> When I delete a competency record [sic] I need all references to this record [sic] to be deleted. When I delete a user I want to set the foreign key to zero (the record [sic] must remain on the database). <<


One of the first steps to learning to think in SQL is that a row and a
record are totally different. Until then, you will keep setting flags
(like your "foreign keys are set to zero" violation of 1NF).

You are trying to keep a history, so you need durations in the data
model.


CREATE TABLE Assignments
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
dot_code CHAR(6) NOT NULL
REFERENCES Competencies(dot_code)
ON UPDATE CASCADE,
assignment_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
completion_date DATETIME
DEFAULT CURRENT_TIMESTAMP
CHECK (assignment_date < completion_date),
PRIMARY KEY (user_id, dot_code, assignment_date),
approving_user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE);


This is proper SQL, but SQL Server will not allow it. The product
worries about cycles in DRI.


One kludge might be to split out the approvers


CREATE TABLE Competencies
(dot_code CHAR(6) NOT NULL PRIMARY KEY,
dot_description VARCHAR(25) NOT NULL);


CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type CHAR(1) DEFAULT 'R' NOT NULL
CHECK (user_type IN ('R', 'A')), -- r= regular, a = approver
PRIMARY KEY (user_id, user_type),
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(25) NOT NULL);


CREATE TABLE Approvers
(user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL
CHECK (user_type = 'A'),
PRIMARY KEY (user_id, user_type),
FOREIGN KEY (user_id, user_type)
REFERENCES Users (user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE);


CREATE TABLE RegularUsers
(user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL
CHECK (user_type = 'R'),
PRIMARY KEY (user_id, user_type),
FOREIGN KEY (user_id, user_type)
REFERENCES Users (user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE);


CREATE TABLE Assignments
(user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL CHECK (user_type = 'R')
FOREIGN KEY (iser_id, user_type)
REFERENCES RegularUsers(user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE,
dot_code CHAR(6) NOT NULL
REFERENCES Competencies(dot_code)
ON UPDATE CASCADE,
assignment_date DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL,
completion_date DATETIME
DEFAULT CURRENT_TIMESTAMP
CHECK (assignment_date < completion_date),
PRIMARY KEY (user_id, dot_code, assignment_date),
approving_user_id INTEGER NOT NULL,
user_type CHAR(1) NOT NULL CHECK (user_type = 'A')
FOREIGN KEY (user_id, user_type)
REFERENCES ApprovingUsers(user_id, user_type)
ON DELETE CASCADE
ON UPDATE CASCADE);