When I run following script, I got the error "The referential relationship will result in a cyclical reference that is not allowed. [ Constraint name = FK_CALENDAR_2 ]".
Code:CREATE TABLE [USER_DEFINED_DAY_TYPE] (
[ID_USER_DEFINED_DAY] int NOT NULL IDENTITY
, [ID_PANEL] int NULL
, [ID_INDEX] int NULL
, [USER_DAY_NAME] nvarchar(100) NULL
, [USER_DAY_CUSTOM_NAME] nvarchar(100) NULL
, CONSTRAINT UQ_USER_DEFINED_DAY_TYPE_1 UNIQUE (ID_PANEL, ID_INDEX)
, CONSTRAINT FK_USER_DEFINED_DAY_TYPE_1 FOREIGN KEY (ID_PANEL)
REFERENCES PANELS (ID_PANEL) ON DELETE CASCADE
);
ALTER TABLE [USER_DEFINED_DAY_TYPE] ADD CONSTRAINT [PK_USER_DEFINED_DAY_TYPE] PRIMARY KEY ([ID_USER_DEFINED_DAY]);
CREATE TABLE [CALENDAR] (
[ID_PANEL] int NULL
, [CALENDAR_DATE] datetime NULL
, [ID_USER_DEFINED_DAY] int NULL
, [ID_CALENDAR] int NOT NULL IDENTITY
, CONSTRAINT FK_CALENDAR_1 FOREIGN KEY (ID_PANEL)
REFERENCES PANELS (ID_PANEL) ON DELETE CASCADE
, CONSTRAINT FK_CALENDAR_2 FOREIGN KEY (ID_USER_DEFINED_DAY)
REFERENCES USER_DEFINED_DAY_TYPE (ID_USER_DEFINED_DAY) ON DELETE CASCADE
);
ALTER TABLE [CALENDAR] ADD CONSTRAINT [PK__CALENDAR__0000000000000A53] PRIMARY KEY ([ID_CALENDAR]);
I understand that there will be no error if the deletion rule is removed or changed to NO ACTION (instead of CASCADE, SET DEFAULT, SET NULL), which is the default one. Also in case of multiple foreign key constraints, one foreign key constraint with "ON DELETE CASCADE" is also fine as long as the others don't have "ON DELETE CASCADE".
How can I have multiple foreign key constraints with "ON DELETE CASCADE"? Can you please help?
