时间戳

首页 > 解决方案 > 如何保证表中两个可为空的列之间只存在一个值

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

问题描述

user有两个 FK，一个 tostudent和第二个 to teacher，都可以为空。

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

我需要什么时候employeeId为空，teacherId必须存在，当employeeId存在时，teacherId必须为空。

永远不要两者都为 null 或两者都存在。在创建表期间有什么方法可以保证这种行为？

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

标签： mysqldatabasedatabase-design

解决方案

您可以使用 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<>小提琴演示

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;

推荐阅读