如何在多个表之间强制实施唯一约束？

Question

我创建了以下两个表格来映射学生和老师：

CREATE TABLE students(
    student_id SERIAL PRIMARY KEY,
    first_name NOT NULL VARCHAR(50),
    last_name NOT NULL VARCHAR(50),
    phone VARCHAR(15) UNIQUE NOT NULL CHECK (phone NOT LIKE '%[^0-9]%'),
    email VARCHAR(30) UNIQUE NOT NULL CHECK (email NOT LIKE '%@%'),
    graduationYear SMALLINT CHECK (graduationYear > 1900)
);

CREATE TABLE teachers(
    teacher_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    departament VARCHAR(40) NOT NULL,
    email VARCHAR(30) UNIQUE NOT NULL CHECK (email NOT LIKE '%@%'),
    phone VARCHAR(15) UNIQUE NOT NULL CHECK (phone NOT LIKE '%[^0-9]%')
);

如您所见，两个表都有一个 phone 和 email 的列。我希望这两个字段对每个人都是 唯一的。

我该如何引入一个约束条件来检查在 students 表中输入的电话号码/电子邮件是否已经存在于 teachers 表中？是否有任何类似于 UNIQUE 但用于多个表的关键字，或者我应采取其他方法？

编辑：正如 @a_horse_with_no_name 指出的，LIKE 不支持正则表达式。我应该使用 SIMILAR TO。

- ROBlackSnail

---

与您的问题无关，但 LIKE 不支持正则表达式。如果您想坚持使用标准 SQL，则需要使用 similar to；或者使用 posix regex。 - user330315

Answer 1

我会创建一个名为 person 的单个表，其中包含教师和学生的通用属性，包括一个标识教师和学生的 type 列。然后您可以在电话和电子邮件列上创建唯一约束（或索引）。

为了存储“特定于类型”的属性（毕业年份、系），您可以在 person 表中具有可空列，并根据类型只放入值。如果您不希望有更多除这两个属性之外的“特定于类型”的属性，则这可能是最简单的解决方案。

如果您预期会有更多“特定于类型”的属性，则还可以使用其他表（student 和 teacher）来包含这些属性。这是关系数据库建模继承的传统方式。由于Postgres支持表继承，因此您也可以创建 teacher 和 student 表以从 person 表继承。

- user330315