如何实现带共享外键的多对多关系？强制关联实体同属父实体

完全可以通过数据库层面的约束来实现这个需求，下面提供两种可靠的方案，其中第一种兼容性和性能更优：

方案一：带文件夹ID的复合外键约束（推荐） #

这种方法通过在多对多关联表中引入folder_id，结合复合外键和唯一键强制数据一致性，几乎所有主流关系型数据库都支持。

1. 基础表结构调整 #

首先确保演示文稿表和照片表中，folder_id与主键的组合是唯一的：

-- 文件夹表
CREATE TABLE folders (
    folder_id INT PRIMARY KEY AUTO_INCREMENT,
    folder_name VARCHAR(100) NOT NULL
);

-- 演示文稿表：添加(folder_id, presentation_id)唯一键
CREATE TABLE presentations (
    presentation_id INT PRIMARY KEY AUTO_INCREMENT,
    folder_id INT NOT NULL,
    presentation_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (folder_id) REFERENCES folders(folder_id),
    UNIQUE KEY uk_presentation_folder (folder_id, presentation_id)
);

-- 照片表：添加(folder_id, photo_id)唯一键
CREATE TABLE photos (
    photo_id INT PRIMARY KEY AUTO_INCREMENT,
    folder_id INT NOT NULL,
    photo_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (folder_id) REFERENCES folders(folder_id),
    UNIQUE KEY uk_photo_folder (folder_id, photo_id)
);

2. 关联表的约束设置 #

在多对多关联表中同时存储folder_id、presentation_id、photo_id，并建立两个复合外键分别指向演示文稿表和照片表：

CREATE TABLE presentation_photo (
    folder_id INT NOT NULL,
    presentation_id INT NOT NULL,
    photo_id INT NOT NULL,
    PRIMARY KEY (folder_id, presentation_id, photo_id),
    -- 确保关联的演示文稿属于当前folder_id
    FOREIGN KEY (folder_id, presentation_id) REFERENCES presentations(folder_id, presentation_id),
    -- 确保关联的照片属于当前folder_id
    FOREIGN KEY (folder_id, photo_id) REFERENCES photos(folder_id, photo_id)
);

当尝试插入跨文件夹的关联记录时（比如演示文稿属于folder1，照片属于folder2），数据库会直接抛出外键约束错误，阻止非法操作。

方案二：使用CHECK约束（部分数据库支持） #

如果你的数据库支持行级CHECK约束（如PostgreSQL 9.3+、MySQL 8.0.16+），可以直接在关联表中添加检查规则，确保关联的演示文稿和照片属于同一文件夹：

CREATE TABLE presentation_photo (
    presentation_id INT NOT NULL,
    photo_id INT NOT NULL,
    PRIMARY KEY (presentation_id, photo_id),
    FOREIGN KEY (presentation_id) REFERENCES presentations(presentation_id),
    FOREIGN KEY (photo_id) REFERENCES photos(photo_id),
    CHECK (
        (SELECT folder_id FROM presentations WHERE presentation_id = presentation_photo.presentation_id) =
        (SELECT folder_id FROM photos WHERE photo_id = presentation_photo.photo_id)
    )
);

这种方案不需要修改基础表，但每次插入或更新记录时都会执行子查询验证，性能略低于方案一，且兼容性不如方案一。

内容的提问来源于stack exchange，提问作者James