我创建了以下两张用于映射学生与教师的表：

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 SIMILAR TO '[0-9]+'),
 email VARCHAR(30) UNIQUE NOT NULL CHECK (email SIMILAR TO '%@%'),
 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 SIMILAR TO '%@%'),
 phone VARCHAR(15) UNIQUE NOT NULL CHECK (phone SIMILAR TO '[0-9]+')
);

编辑：正如@a_horse_with_no_name所指出，LIKE不支持正则表达式，我已经修正为使用SIMILAR TO。

目前两张表各自的phone和email字段都有单表唯一约束，但我希望这两个字段对每个个体保持全局唯一——也就是新增到students表的手机号/邮箱不能已经存在于teachers表中，反之亦然。

请问如何添加此类约束？是否存在类似UNIQUE的跨表约束关键字，或是需要采用其他方案？

从你的表结构里的SERIAL能看出来用的是PostgreSQL对吧？PG本身并没有直接支持跨表的UNIQUE约束关键字，但有几种靠谱的方案能实现你要的全局唯一效果，我按推荐程度给你梳理下：

方案1：抽离共享唯一表（最规范的范式化方案） #

这是最符合数据库设计原则的做法——把需要全局唯一的phone和email抽出来放到一个单独的共享表，让学生和教师表通过外键关联到这个表，利用共享表的唯一约束来实现全局唯一性。

具体操作： #

1. 先创建共享的联系方式表：

CREATE TABLE unique_contacts (
    contact_id SERIAL PRIMARY KEY,
    phone VARCHAR(15) UNIQUE NOT NULL CHECK (phone SIMILAR TO '[0-9]+'),
    email VARCHAR(30) UNIQUE NOT NULL CHECK (email SIMILAR TO '%@%'),
    -- 加个类型字段区分是学生还是教师，避免数据混乱（可选但推荐）
    entity_type VARCHAR(10) NOT NULL CHECK (entity_type IN ('student', 'teacher')),
    -- 确保每个联系方式只能绑定一个实体
    CONSTRAINT unique_phone_for_entity UNIQUE (phone),
    CONSTRAINT unique_email_for_entity UNIQUE (email)
);

2. 修改原有的学生和教师表，移除原有手机号/邮箱字段，添加外键关联：

-- 更新students表
ALTER TABLE students
DROP COLUMN phone,
DROP COLUMN email,
ADD COLUMN contact_id INT NOT NULL REFERENCES unique_contacts(contact_id);

-- 更新teachers表
ALTER TABLE teachers
DROP COLUMN phone,
DROP COLUMN email,
ADD COLUMN contact_id INT NOT NULL REFERENCES unique_contacts(contact_id);

以后新增学生或教师时，必须先在unique_contacts里创建对应的联系方式，共享表的唯一约束会自动拦截重复的手机号和邮箱，从根源上避免跨表重复。

方案2：用触发器做跨表检查（无需改结构的灵活方案） #

如果不想动现有表结构，触发器是个不错的选择。你可以给两个表分别加插入/更新触发器，在数据变更前检查另一个表有没有重复的联系方式。

示例操作： #

1. 先写一个检查学生联系方式的触发器函数：

CREATE OR REPLACE FUNCTION check_student_contact_uniqueness()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查手机号是否在教师表已存在
    IF EXISTS (SELECT 1 FROM teachers WHERE phone = NEW.phone) THEN
        RAISE EXCEPTION '手机号 % 已存在于教师表中', NEW.phone;
    END IF;
    -- 检查邮箱是否在教师表已存在
    IF EXISTS (SELECT 1 FROM teachers WHERE email = NEW.email) THEN
        RAISE EXCEPTION '邮箱 % 已存在于教师表中', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 把这个函数绑定到students表的插入和更新操作上：

CREATE TRIGGER trigger_student_contact_check
BEFORE INSERT OR UPDATE OF phone, email ON students
FOR EACH ROW EXECUTE FUNCTION check_student_contact_uniqueness();

3. 同理，写一个反向的触发器函数绑定到teachers表，检查学生表的重复值：

CREATE OR REPLACE FUNCTION check_teacher_contact_uniqueness()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM students WHERE phone = NEW.phone) THEN
        RAISE EXCEPTION '手机号 % 已存在于学生表中', NEW.phone;
    END IF;
    IF EXISTS (SELECT 1 FROM students WHERE email = NEW.email) THEN
        RAISE EXCEPTION '邮箱 % 已存在于学生表中', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_teacher_contact_check
BEFORE INSERT OR UPDATE OF phone, email ON teachers
FOR EACH ROW EXECUTE FUNCTION check_teacher_contact_uniqueness();

这个方案的好处是不用改现有表，但要注意：高并发场景下可能出现竞态条件（比如两个事务同时插入相同手机号，各自检查时都没发现重复，最后都插入成功）。如果要避免这个问题，可以在触发器里加行级锁，或者优先考虑方案1。

方案3：EXCLUDE约束（进阶小众方案） #

PG的EXCLUDE约束可以实现复杂的唯一性规则，但这个方案相对复杂，维护成本高，一般只在前面两种方案不适用时才考虑。

简单示例（需要先启用扩展）： #

1. 先启用btree_gist扩展：

CREATE EXTENSION IF NOT EXISTS btree_gist;

2. 给students表添加跨表的EXCLUDE约束：

ALTER TABLE students
ADD CONSTRAINT exclude_duplicate_phone_across_tables
EXCLUDE USING gist (phone WITH =) WHERE (EXISTS (SELECT 1 FROM teachers WHERE teachers.phone = students.phone));

这种方式的实用性不如前两种，所以不优先推荐。

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