Foreign Key Constraints may cause cycles or multip

问题:

Possible Duplicate:
Foreign key constraint may cause cycles or multiple cascade paths?

I am getting the following error while creating the table listed below.

Msg 1785, Level 16, State 0, Line 1 Introducing FOREIGN KEY constraint 'FKFacSupervisor' on table 'Faculty' 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.

CREATE TABLE Faculty
(
    FacNo               CHAR (11),
    FacFirstName        VARCHAR (50)CONSTRAINT FacFirstNameRequired NOT NULL,
    FacLastName         VARCHAR (50) CONSTRAINT FacLastNameRequired NOT NULL,
    FacCity             VARCHAR (50) CONSTRAINT FacCityRequired NOT NULL,
    FacState            CHAR (2) CONSTRAINT FacStateRequired NOT NULL,
    FacZipCode          CHAR (10) CONSTRAINT FacZipCodeRequired NOT NULL,
    FacHireDate         DATE,
    FacDept             CHAR (6),
    FacRank             CHAR (4),
    FacSalary           DECIMAL (10,2),
    FacSupervisor       CHAR (11), 
CONSTRAINT PKFaculty PRIMARY KEY (FacNo),
CONSTRAINT FKFacSupervisor FOREIGN KEY (FacSupervisor) REFERENCES Faculty
    ON DELETE SET NULL
    ON UPDATE CASCADE
)

回答1:

The message is telling you that your foreign key constraint FKFacSupervisor cannot be created because this constraint could cause cycles or multiple cascade paths. That means that the constraint could result in a situation where updates or deletes to one row could cause updates or deletes to another row, which could in turn cause updates or deletes to the first row. The message also explains possible solutions: Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other constraints. Only by knowing the schema and the expected usage patterns can you decide which of those options will be best.