mysql - 有两个外键约束引用相同的主键并且都使用 ON UPDATE CASCADE

Question

我在使用 mysql management studio 时遇到一些问题，它给我错误

Msg 1785, Level 16, State 0, Line 65
Introducing FOREIGN KEY constraint 'FK__Likes__LikeeId__5A846E65' on table 'Likes' 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 65
Could not create constraint or index. See previous errors.

这是与 Likes 表相关的代码，profile 表是我们从中获取外键的地方。问题是它说我不能同时拥有外键约束，因为它会创建一个循环，但我不明白为什么会这样。

CREATE TABLE Profile (
ProfileId VARCHAR(24),
OwnerSSN VARCHAR(11) NOT NULL,
Age INTEGER NOT NULL,
AgeRangeStart INTEGER NOT NULL,
AgeRangeEnd INTEGER NOT NULL,
GeoRange INTEGER NOT NULL,
Gender VARCHAR(1) NOT NULL,
Hobbies VARCHAR(50),
Height DECIMAL(2,1) NOT NULL,
Weight INTEGER NOT NULL,
HairColor VARCHAR(20),
ProfileCreationDate DATETIME NOT NULL,
ProfileModDate DATETIME NOT NULL,
Active BIT NOT NULL,
PRIMARY KEY(ProfileId),
FOREIGN KEY (OwnerSSN) REFERENCES Customer(SSN),
CHECK(Age < 120 AND Age >= 17),
CHECK(AgeRangeStart >= 17 AND AgeRangeEnd >= AgeRangeStart),
CHECK(GeoRange > 0 ) );

CREATE TABLE Likes (
LikerId VARCHAR(24),
LikeeId VARCHAR(24),
Date_Time DATETIME ,
PRIMARY KEY (LikeeId, LikerId, Date_Time) ,
FOREIGN KEY(LikerId) REFERENCES Profile(ProfileId)
ON UPDATE CASCADE,
FOREIGN KEY(LikeeId) REFERENCES Profile(ProfileId)
ON UPDATE CASCADE
);

Answer 1

这是您需要开始使用触发器的时候。

CREATE TRIGGER tr_UpdateLikee BEFORE UPDATE
ON Profile FOR EACH ROW
UPDATE Likes 
SET likeeId = NEW.ProfileId
Where LikeeId = Profile.ProfileId

CREATE TRIGGER tr_UpdateLiker BEFORE UPDATE
ON Profile FOR EACH ROW
UPDATE Likes 
SET likerId = NEW.ProfileId
Where LikerId = Profile.ProfileId