Referential Integrity problem

[Jeff North]

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?
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Erland Sommarskog]

Jeff North (jn******@yahoo.com.au) writes:

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?

SQL Server is extremly conservative with cascading foreign keys. If there
is the slightest suspecion there could be trouble, it won't permit you
define the cascade path. I can't really say what might be the problem
in this case, but I guess that if you add triggers into the mix, there
can be intersting results.

One way to handle cascading updates and deletes is to use triggers. The
drawback with this is that you cannot have any declarative referential
integrity at all, as constraints are checked before triggers fire.

A better approach is probably to use a stored procedure that performs
the cascade from bottom up. That is, it first deletes the children and
then the children.

By the way, having 0 as a dummy user for "no one at all", is probably
not the best design. I would rather use NULL for this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff North]

On Sat, 3 Jun 2006 17:34:14 +0000 (UTC), in
comp.databases.ms-sqlserver Erland Sommarskog <es****@sommarskog.se>
<Xn**********************@127.0.0.1> wrote:

| Jeff North (jn******@yahoo.com.au) writes:
| > 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?
Thanks for your response, much appreciated.
| SQL Server is extremly conservative with cascading foreign keys. If there
| is the slightest suspecion there could be trouble, it won't permit you
| define the cascade path.
Thank goodness it's the app. I thought I was misunderstanding
something.
| I can't really say what might be the problem
| in this case, but I guess that if you add triggers into the mix, there
| can be intersting results.
No triggers at present but it looks as though triggers might be the
only option.
| One way to handle cascading updates and deletes is to use triggers. The
| drawback with this is that you cannot have any declarative referential
| integrity at all, as constraints are checked before triggers fire.
|
| A better approach is probably to use a stored procedure that performs
| the cascade from bottom up. That is, it first deletes the children and
| then the children.
More maintenance woes :-(
| By the way, having 0 as a dummy user for "no one at all", is probably
| not the best design. I would rather use NULL for this.

Agreed but there are always exceptions to the rule :-)
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Erland Sommarskog]

Jeff North (jn******@yahoo.com.au) writes:

On Sat, 3 Jun 2006 17:34:14 +0000 (UTC), in
comp.databases.ms-sqlserver Erland Sommarskog <es****@sommarskog.se>
<Xn**********************@127.0.0.1> wrote:

| I can't really say what might be the problem
| in this case, but I guess that if you add triggers into the mix, there
| can be intersting results.

No triggers at present but it looks as though triggers might be the
only option.

What I wanted to say that combination of cascading DRI (had it been
permitted) and triggers in this case could have lead to interesting
results. And since a trigger could be added later, my suspicion that the
mere possibility is enough to disallow the multiple cascade path.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Jeff North]

On Sun, 4 Jun 2006 16:22:48 +0000 (UTC), in
comp.databases.ms-sqlserver Erland Sommarskog <es****@sommarskog.se>
<Xn**********************@127.0.0.1> wrote:

| Jeff North (jn******@yahoo.com.au) writes:
| > On Sat, 3 Jun 2006 17:34:14 +0000 (UTC), in
| > comp.databases.ms-sqlserver Erland Sommarskog <es****@sommarskog.se>
| ><Xn**********************@127.0.0.1> wrote:
| >>| I can't really say what might be the problem
| >>| in this case, but I guess that if you add triggers into the mix, there
| >>| can be intersting results.
| >
| > No triggers at present but it looks as though triggers might be the
| > only option.
|
| What I wanted to say that combination of cascading DRI (had it been
| permitted) and triggers in this case could have lead to interesting
| results.
I could well imagine what sort of 'problems' could arise :-)
| And since a trigger could be added later, my suspicion that the
| mere possibility is enough to disallow the multiple cascade path.

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[--CELKO--]

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);

[Jeff North]

On 5 Jun 2006 10:52:35 -0700, in comp.databases.ms-sqlserver
"--CELKO--" <jc*******@earthlink.net>
<11**********************@i39g2000cwa.googlegroups .com> wrote:

| 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?
Please don't jump to unnecassary, and wrong, conclussions.
I'm converting the database from mySQL4.0.21 to MSSQL therefore the
schema hasn't been fully optimised.
| Can you use the "Dictionary of Occupational Titles" for
| Competencies? What research did you do?
Again, you've seen 3 partial tables and jump to unnecessary
conclusions.
| Why is a user an attribute of a Competency? Only Fred can be a
| carpenter? Why do you use bit flags in SQL?
They, bit flags, are the most appropriate type for true/false or
yes/no or in/out data fields, yes?
| 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);
Again, you're jumping to wrong conclusions.
The usr_info table is for registered users that can add, delete and
modify data. I need to track who has done what as part of the business
rules for the application (as can be seen by the ApprovedBy and
LockedBy fields).
| >> 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.
Please explain this further.
| Until then, you will keep setting flags
| (like your "foreign keys are set to zero" violation of 1NF).
|

I've snipped your code but will study it further.
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------