mysql - 如何保证表中两个可为空的列之间只存在一个值

Question

user 有两个 FK，一个给 student，第二个给 teacher，都可以为 null。

create table user(
  employeeId int null,
  teacherId  int null
  foreign key (employeeId) references employee (id),
  foreign key (teacherId) references teacher (id)
);

我需要当employeeId为null时，teacherId必须存在，当employeeId存在时，teacherId必须为空。

从不都为空或都存在。有什么办法可以保证在创建表时有这种行为吗？

类表继承不适合我的情况。

Answer 1

您可以使用 CHECK(MySQL 8.0.16+):

create table user(
  employeeId int null,
  teacherId  int null,
  foreign key (employeeId) references employee (id),
  foreign key (teacherId) references teacher (id),
  CHECK ((employeeId IS NULL) + (teacherId IS NULL) = 1)
);

db<>fiddle demo

---

CREATE TABLE employee(id INT PRIMARY KEY);
INSERT INTO employee VALUES(1);

CREATE TABLE teacher(id INT PRIMARY KEY);
INSERT INTO teacher VALUES(1);

create table user(
  employeeId int null,
  teacherId  int null,
  foreign key (employeeId) references employee (id),
  foreign key (teacherId) references teacher (id),
  CHECK ((employeeId IS NULL) + (teacherId IS NULL) = 1)
);

INSERT INTO user VALUES(NULL,NULL);
-- Check constraint 'user_chk_1' is violated.

INSERT INTO user VALUES(1,1);
-- Check constraint 'user_chk_1' is violated.

INSERT INTO user VALUES(1,NULL);
INSERT INTO user VALUES(NULL,1);

SELECT * FROM user;