时间戳

首页 > 解决方案 > 创建两个具有相同主键的子类型（或子）表，这也是父表 sql 的外键

mysql - 创建两个具有相同主键的子类型（或子）表，这也是父表 sql 的外键

问题描述

我有以下表格：

CREATE TABLE employee(
    id INT(11) UNSIGNED PRIMARY KEY,
    namee VARCHAR(60) NOT NULL)
ENGINE = InnoDB;

CREATE TABLE consierge(
    id INT(11) UNSIGNED PRIMARY KEY,
    namee VARCHAR(60) NOT NULL, 
    zonee VARCHAR(20), 
    functionn VARCHAR(20),
    FOREIGN KEY (id) REFERENCES employee(id))
ENGINE = InnoDB;

CREATE TABLE guard(
    id INT(11) UNSIGNED PRIMARY KEY,
    namee VARCHAR(60) NOT NULL, 
    rol VARCHAR(20) NOT NULL,
    wtime ENUM ('Day','Night'),
    FOREIGN KEY (id) REFERENCES employee(id)) 
ENGINE = InnoDB;

当我尝试创建一个看门人或警卫时，它首先验证员工中是否存在该 ID，这很好。问题是，即，当一个 id 已经在看守（因此在员工中）并且我创建一个具有相同 id 的 consierge 时，consierge 被创建，因此，一个员工可以是看守和看门人；我希望员工可以独家看守或看管。

标签： mysqlsql

解决方案

您可以使用一些附加逻辑和生成的列来执行此操作（无需触发器）：

CREATE TABLE employee(
    id INT(11) UNSIGNED PRIMARY KEY,
    namee VARCHAR(60) NOT NULL,
    type varchar(255),
    c_id int generated always as (case when type = 'c' then id end) stored,
    g_id int generated always as (case when type = 'g' then id end) stored,
    check (type in ('c', 'g'))
);

CREATE TABLE consierge (
    id INT(11) UNSIGNED PRIMARY KEY,
    namee VARCHAR(60) NOT NULL, 
    zonee VARCHAR(20), 
    functionn VARCHAR(20),
    type varchar(255) generated always as ('c') stored,
    unique (type, id),
    FOREIGN KEY (id) REFERENCES employee(id)
);

CREATE TABLE guard(
    id INT(11) UNSIGNED PRIMARY KEY,
    namee VARCHAR(60) NOT NULL, 
    rol VARCHAR(20) NOT NULL,
    wtime ENUM ('Day','Night'),
    type varchar(255) generated always as ('g') stored,
    unique (stored, id),
    FOREIGN KEY (id) REFERENCES employee(id)
);

ALTER TABLE employee ADD CONSTRAINT fk_employee_c_id
    FOREIGN KEY (c_id) REFERENCES concierge(id);

ALTER TABLE employee ADD CONSTRAINT fk_employee_g_id
    FOREIGN KEY (g_id) REFERENCES guard(id);

这会添加计算列以允许所需的外键关系。

推荐阅读