January 10, 2009 at 9:02 pm
Hi there! I dont know if I posted on the appropriate forum. Please move this to the appropriate forum if applicable. Thanks!
I have 2 tables: 1. Employees 2. Vouchers
Employees table has a single primary key. Vouchers table has 3 foreign key constraints referencing the Employees table.
The following is a sample T-SQL script (not the actual table script) to create both tables and their relationship in SQL Server:
IF OBJECT_ID('dbo.Vouchers') IS NOT NULL
DROP TABLE dbo.Vouchers
IF OBJECT_ID('dbo.Employees') IS NOT NULL
DROP TABLE dbo.Employees
GO
CREATE TABLE Employees
(
ObjectID INT NOT NULL PRIMARY KEY IDENTITY
)
CREATE TABLE Vouchers
(
ObjectID INT NOT NULL PRIMARY KEY IDENTITY,
IssuedBy INT,
ReceivedBy INT,
ApprovedBy INT,
CONSTRAINT fk_Vouchers_Employees_IssuedBy FOREIGN KEY (IssuedBy)
REFERENCES Employees (ObjectID)
ON UPDATE CASCADE
ON DELETE NO ACTION,
CONSTRAINT fk_Vouchers_Employees_ReceivedBy FOREIGN KEY (ReceivedBy)
REFERENCES Employees (ObjectID)
ON UPDATE CASCADE
ON DELETE NO ACTION,
CONSTRAINT fk_Vouchers_Employees_ApprovedBy FOREIGN KEY (ApprovedBy)
REFERENCES Employees (ObjectID)
ON UPDATE CASCADE
ON DELETE NO ACTION
)
But an error is thrown:
Msg 1785, Level 16, State 0, Line 7
Introducing FOREIGN KEY constraint 'fk_Vouchers_Employees_ReceivedBy' on table 'Vouchers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
I don't have an idea of what efficient solution is available here. The requirements on the relationship is that: whenever an Employee is deleted, the Voucher that references some of its columns to the Employee does not get deleted (ON DELETE CASCADE is not an option). Instead, the values of the columns (IssuedBy, ReceivedBy and/or ApprovedBy) that are referenced to the deleted Employee should be set to NULL (since the columns are NULLABLE).
Many thanks!
January 11, 2009 at 11:33 am
The problem is coming from the UPDATE CASCADE, not the DELETE settings. I recommend that you get rid of it.
I am not normally in favor of the CASCADE features in any event, they are not really relational or part of the relational model and I think that it is a bad idea in general to mix the Constraint features with what should be application or code responsibilities (which is what the cascade is). However, the Update Cascade seems particularily inappropiate, particularily for Identity Key columns.
What you are saying with your three UPDATE CASCADES on the Employees.ObjectID, is that if you execute a statement like this:
UPDATE employees
SET ObjectID = @NewValuethen you want all of the Vouchers fields that reference that Employees.ObjectID value to be changed also. But A) I doubt that you would normally ever change Employees.ObjectID, and B) If you did, you might not want to change the Vouchers references (or have an) and C) you can not normally write to an Identity field in SQL Server anyway.
So just remove them and that should solve your problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 11, 2009 at 7:58 pm
thanks a lot Barry. looks like i'll use triggers instead to maintain data integrity. 🙂
January 11, 2009 at 10:14 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply