有两个外键约束引用相同的主键，都使用ON UPDATE CASCADE [英] Having Two Foreign Key Constraints Reference the Same Primary Key and both use ON UPDATE CASCADE

问题描述

我在使用mysql管理工作室时遇到了一些麻烦，它给了我错误信息。

I am having some trouble with mysql management studio where it is giving me the error

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.

这里是与喜欢表相关的代码，个人资料表是我们得到我们的外键从。这个问题是说，我不能同时存在外键约束，因为它创建了一个循环，但我不明白为什么会这样。

Here is the code related to Likes table, The profile table is the one where we get our foreign key from. The issue is its saying i cant have both the foreign key constraints because it creates a cycle, but I don't see why this is the case.

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
);

推荐答案

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

This is when you need to start using triggers.

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

这篇关于有两个外键约束引用相同的主键，都使用ON UPDATE CASCADE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！