MySQL 5.5继承关系模型中的完整性约束问题

Question

我有以下数据库关系模式，旨在对 EMP 超类型以及两个 FULL_TIME_EMP 和 PART_TIME_EMP 子类型进行建模：

我遇到的问题是完整性问题，即我想确保全职员工只能在 FULL_TIME_EMP 表中拥有相应的行，同样，兼职员工只能在 PART_TIME_EMP 表中拥有相应的行。

正如您将从下面的屏幕截图中看到的那样，不强制执行此完整性约束。

这是 EMP_TYPE 表：

和 EMP 表：

PART_TIME_EMP 表：

最后是 FULL_TIME_EMP 表，它显示了完整性违规！

有没有办法通过改变我的数据库模型设计来强制执行此完整性约束，或者我是否必须诉诸触发器？

问候，

Answer 1

您不必求助于触发器，但它可能会有所帮助。改变结构会有很大帮助，但它通常依赖于 CHECK 约束。在MySQL中，如果需要CHECK约束，则必须编写触发器或添加另一个使用外键约束的表。

您还有额外的负担，因为兼职员工后来可能成为全职员工，反之亦然。

下面是我如何为更标准的 SQL 平台编写它。

create table emp (
  emp_id integer primary key,
  emp_type char(1) not null check (emp_type in ('f', 'p')), -- FK in your schema
  emp_name varchar(50) not null,
  start_date date not null default current_date,
  unique (emp_id, emp_type)
);

create table full_time_emp (
  emp_id integer primary key references emp (emp_id),
  emp_type char(1) not null default 'f' check (emp_type = 'f'),
  foreign key (emp_id, emp_type) references emp (emp_id, emp_type),
  salary decimal(14,2) not null check (salary > 15000)
);

create table part_time_emp (
  emp_id integer primary key references emp (emp_id),
  emp_type char(1) not null default 'p' check (emp_type = 'p'),
  foreign key (emp_id, emp_type) references emp (emp_id, emp_type),
  rate decimal(14,2) not null check (rate > 0 and rate < 100)
);

在表 full_time_emp 中，这两个约束

  check (emp_type = 'f'),
  foreign key (emp_id, emp_type) references emp (emp_id, emp_type),

一起防止兼职员工出现在全职表中。如果“emp”中引用的行包含“p”，则外键引用将失败；如果您尝试将“p”插入“full_time_emp”，检查约束将失败。

在这种情况下，您可以用单行表和外键引用替换检查约束。该表应仅包含“f”。 （或者您用来代表全职员工的任何 ID 号。）同样的事情也适用于“part_time_emp”。因此，您可以对 full_time_emp 执行此操作，而不是 CHECK 约束。

create table full_time_emp_check (
  full_time_emp_type char(1) primary key
);
insert into full_time_emp_check values ('f');

alter table full_time_emp
add constraint full_time_emp_check
foreign key (emp_type) references full_time_emp_check (full_time_emp_type);

part_time_emp 的更改类似。您可能需要在这两个表上都有一个触发器，以确保它们永远不会包含多于一行。

甚至对工资和费率的检查也可以通过附加表格和外键引用来实现。不过，这有点像把死去的鲸鱼踢到海滩上。我可能会使用 MySQL 中的触发器来实现那种范围约束。

恕我直言，对于员工类型来说，CHAR(1) 代码比整数更好。人类可读的代码不需要联接。