mysql - 强制唯一性并从多个表定义外键约束

Question

这个问题适用于不同的RDBMS:MySQL 和SQL Server

我整晚都在找这个，但我在网上找不到。我的问题是关于唯一性 和外键。考虑以下架构:

CREATE TABLE Male
(
    StudentID INT,
    FirstName VARCHAR(20) NOT NULL,
    MiddleName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    -- other columns ...
    CONSTRAINT male_PK PRIMARY KEY (StudentID),
    CONSTRAINT male_UQ UNIQUE (FirstName, MiddleName, LastName)
);

CREATE TABLE Female
(
    StudentID INT,
    FirstName VARCHAR(20) NOT NULL,
    MiddleName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    -- other columns ...
    CONSTRAINT Female_PK PRIMARY KEY (StudentID),
    CONSTRAINT Female_UQ UNIQUE (FirstName, MiddleName, LastName)
);

CREATE TABLE ClassList
(
    ClassID INT,
    Name VARCHAR(30),
    ClassYear INT,
    -- other columns ...
    CONSTRAINT ClassList_PK PRIMARY KEY (ClassID),
    CONSTRAINT ClassList_UQ UNIQUE (Name, ClassYear)
);

Male表和Female表之所以分开是因为它们由不同的sql帐户维护。

---

现在的问题是关联类。

CREATE Student_Class
(
    SudentID INT,
    ClassID INT,
    CONSTRAINT tb_UQ UNIQUE (StudentID, ClassID)
)

所以我的问题是:

• 如何设置 Male 和 Female 表中 StudentID 的唯一性。我在网上找不到那个。
• 如何强制执行外键约束，其中关联类表的值列来自两个表:Male 和Female？

也接受建议。

谢谢

Answer 1

这就是属性拆分不好的原因 - 它开始以奇怪的方式“感染”其他表。

你可以拥有:

CREATE Student_Class
(
    MaleStudentID INT NULL,
    FemaleStudentID INT NULL,
    ClassID INT NOT NULL,
    CONSTRAINT tb_UQ UNIQUE (MaleStudentID, FemaleStudentID, ClassID)
)

据我所知，由于 MySQL 不支持 CHECK 约束，因此您还必须创建一个触发器来确保只有一个且恰好是一个 MaleStudentID 和 FemaleStudentID 是 NOT NULL。

您现在可以以一种明显的方式应用您的外键约束。

---

对于 SQL Server，您将有一个检查约束:

ALTER TABLE Student_Class ADD CONSTRAINT CK_Students_Nullability CHECK (
    (MaleStudentID is null and FemaleStudentID is not null) or
    (MaleStudentID is not null and FemaleStudentID is null)
)