Sql-server – When Creating a Foreign Key Relationship

To further clarify this question, I've created a sample schema as such:

USE TempDB;

CREATE TABLE [dbo].[myObj]
(
    [ID] [int] NOT NULL CONSTRAINT PK_myObj PRIMARY KEY IDENTITY(1,1),
    [fk_ParentID] [int] NULL CONSTRAINT FK_myObj_ID FOREIGN KEY 
                    REFERENCES myObj (ID) ON DELETE CASCADE
);

When you attempt to execute this SQL, the following error is returned by SQL Server:

Msg 1785, Level 16, State 0, Line 3 Introducing FOREIGN KEY constraint 'FK_myObj_ID' on table 'myObj' 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 3 Could not create constraint. See previous errors.

The error message indicates that SQL Server is declining to create the foreign key cascade relationship since deleting a single row could result in all the rows being deleted (or perhaps just multiple rows, not all, depending on data in the table!).

To illustrate this, consider the following data:

INSERT INTO myObj (fk_ParentID) VALUES (NULL);
INSERT INTO myObj (fk_ParentID) VALUES (1);
INSERT INTO myObj (fk_ParentID) VALUES (2);
INSERT INTO myObj (fk_ParentID) VALUES (3);
INSERT INTO myObj (fk_ParentID) VALUES (4);
INSERT INTO myObj (fk_ParentID) VALUES (5);
INSERT INTO myObj (fk_ParentID) VALUES (6);
INSERT INTO myObj (fk_ParentID) VALUES (7);

Which looks like:

If I could define the ON DELETE CASCADE action on this table, executing the following code would delete all rows in the table:

DELETE FROM myObj WHERE ID = 1;

Deleting the row with ID=1 would cascade through the row with fk_ParentID=1 which would cascade delete through fk_ParentID 2, etc, etc. Most likely this is not the effect you want.

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint.

If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update.

EDIT: Since you do not have the ON UPDATE CASCADE constraint, but you want to have that set up, it is a bit of work. SQL Server does not support altering the constraints to a new setting.

It is necessary to iterate through each table that has a FK constraint to the PK table. For each table with the FK:

1. ALTER TABLE to drop the existing FK constraint.
2. ALTER TABLE again to create the ON UPDATE CASCADE constraint for the FK in question.

This takes a bit of effort, but would result in your constraint being properly set for your case.

EDIT 2: The information that you need is found in sys.foreign_keys. You can select from that table to get all the information you need.

A post from John Paul Cook can be found here:

(http://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx)

This code will drop and create ALL FK constraints in a database. You should be able to work from that to make only the changes that you want in your database.