This question already has answers here:
In SQL, is it OK for two tables to refer to each other?
(6个答案)
5年前关闭。
我正在做的一个项目有一个小的关系问题,我试过用谷歌搜索答案,但不知道用什么术语来找到我的答案,哈哈
要解决的问题:
用户可以拥有许多照片,其中一张照片可以是个人资料照片,但只能是一张。
例如:
CREATE TABLE user (
    ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(25) NOT NULL
);
CREATE TABLE photo (
    ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_ID Int NOT NULL,
    imgpath varchar(260) NOT NULL,
    FOREIGN KEY (user_ID) REFERENCES user (ID) ON UPDATE CASCADE
);
CREATE table userprofphoto (
    user_ID Int NOT NULL,
    photo_ID Int NOT NULL,
    FOREIGN KEY (photo_ID) REFERENCES photo (ID) ON UPDATE CASCADE,
    FOREIGN KEY (user_ID) REFERENCES user (ID) ON UPDATE CASCADE
);

因此,获取用户个人资料照片的查询将是:
SELECT imgpath FROM photo WHERE photo_ID=(SELECT photo_ID FROM userprofphoto WHERE user_ID=:userid LIMIT 1)

这是可行的,但它创建了一个关系循环。我应该/可以修这个吗?
我确实考虑过从user_ID表中删除userprofphoto,但这样会增加查询长度:
SELECT imgpath FROM photo
LEFT JOIN userprofphoto ON userprofphoto.photo_ID=photo.ID
WHERE photo.user_ID=:user_id

感谢任何帮助:D

最佳答案

第一个查询没有意义,因为imgpath只在您的设计中的photos中。(顺便说一下,我通常更喜欢给表格取复数而不是单数。它有助于避免与保留字的冲突。)
设计中的一个问题是表userprofphoto对它没有约束。所以一个用户可以有多个个人资料照片。通过在表中的字段user_ID上添加唯一约束或唯一索引,可以很容易地解决此问题。
除了这个小调整,你的设计是好的。还有两种方法。
一种是在photo表中有一个标志,指定将照片用于配置文件。这在MySQL中很难维护。其他数据库具有使这一点更容易实现的功能。
不过,最好的解决方案可能是完全消除userprofphoto表,只需向user添加一列。这会自动强制限制只有一张个人资料照片。当然,如果您稍后决定删除此限制,则需要重新引入userprofphoto表。
您可能还需要一个约束,即用户实际拥有配置文件照片。这需要在MySQL中实现一个触发器。您可能可以在添加和修改配置文件照片的逻辑中处理此问题。
使用此结构,您的查询将是:

SELECT imgpath
FROM users u join
     photos p
     ON u.profile_photo_ID = p.photo_ID;
WHERE u.user_ID = :user_id;

08-06 19:37