mysql - 通过连接表的唯一约束 'owner-owned attribute'

Question

我有以下场景:“电话”子表可以通过连接表为多个父表提供服务，如下所示:

CREATE TABLE phone (

    id BIGINT AUTO_INCREMENT,
    number VARCHAR(16) NOT NULL,
    type VARCHAR(16) NOT NULL,

    PRIMARY KEY(id)     

);

CREATE TABLE employee_phone (

    id BIGINT AUTO_INCREMENT,
    employee BIGINT NOT NULL,
    phone BIGINT NOT NULL,

    PRIMARY KEY(id),
    CONSTRAINT empl_phone_u_phone UNIQUE(phone),
    CONSTRAINT empl_phone_fk_employee
        FOREIGN KEY(employee)
        REFERENCES employee(id) ON DELETE CASCADE,
    CONSTRAINT empl_phone_fk_phone
        FOREIGN KEY(phone)
        REFERENCES phone(id) ON DELETE CASCADE

);

让Alice和Bob住在同一所房子里，并且是同一家公司的员工。 HR 为 Alice 注册了两个电话号码，而他们只为 Bob 注册了一个电话号码，即房子的固定电话号码。 有没有办法在数据库级别强制执行同一员工(或供应商，或稍后出现的任何父级)的电话号码(号码类型)不能重复，使用此配置？或者我会必须在应用层考虑这样的限制吗？我宁愿不使用触发器或表非规范化(如网站上的相关问题所示，如 this one ，它使用 ID，而不是其他字段)，但如果没有其他选择，我愿意这样做。我正在使用MySQL。感谢您的关注。

Answer 1

如果我理解正确，您只需要对连接表进行唯一约束:

alter table employee_phone add constraint unq_employeephone_employee_phone unique (employee, phone);

这将防止给定员工或(具有同等约束)供应商出现重复。

如果您希望 phone 表中的所有电话号码都是唯一的，则只需在 phone 上添加唯一约束/索引即可:

alter table phone add constraint unq_phone_phone unique (phone);

(您可能还想包含类型)。

如果您尝试添加重复的电话，代码将返回错误。