没有 UDF 的 SQL 检查约束

Question

我有以下(虚构的)表格:

╔════════════════════╗        ╔════════════════════╗
║ Course             ║        ║ Person             ║
╠══════╦═════════════╣        ╠══════╦═════════════╣
║ ID   ║ int         ║        ║ ID   ║ int         ║
║ Name ║ varchar(50) ║        ║ Name ║ varchar(50) ║
╚══════╩═════════════╝        ╚══════╩═════════════╝

╔════════════════════╗        ╔═════════════════════╗
║ Occupation         ║        ║ B_Occupation_Person ║
╠══════╦═════════════╣        ╠══════════════╦══════╣
║ ID   ║ int         ║        ║ Person_ID    ║ int  ║
║ Name ║ varchar(50) ║        ║ Ocupation_ID ║ int  ║
╚══════╩═════════════╝        ╚══════════════╩══════╝

╔═════════════════╗
║ B_Course_Person ║
╠═══════════╦═════╣
║ Course_ID ║ int ║
║ Person_ID ║ int ║
╚═══════════╩═════╝

在 Occupation表，有 2 行: Student和 Teacher .
B_Occupation_Person绑定(bind)表允许我给所有人一个职业和 B_Course_Person绑定(bind)表允许我将老师与类(class)相关联。

我的问题是我想确保 B_Course_Person只能包含教师。

我的第一个想法是在这个表上添加一个检查约束，但我只能通过使用 UDF 从 B_Occupation_Person 获取此人的职业来做到这一点。 table 。从我读到的 here ，在检查约束中使用 UDF 是不好的。

我的第二个想法是添加一列 Occupation在 B_Course_Person表，但后来我得到数据冗余......

什么是最好的方法，在这里？

谢谢，

Answer 1

如果您的人员表中有一个“类型”列来区分学生和教师(例如，如果一个人可以两者都是不可能的)，您可以在主键中包含该类型列，然后限制链接表的外键给老师:

create table person 
(
   id integer not null, 
   person_type varchar(10) not null,
   name varchar(100),
   constraint pk_person primary key (id, person_type),
   constraint type_check check (person_type in ('student', 'teacher'))
);

create table b_occupation_person
(
  occupation_id integer not null,
  person_id integer not null,
  person_type varchar(10) not null,
  constraint fk_occupation_person 
      foreign key (person_id, person_type) 
      references person (id, person_type),
  constraint type_check check (person_type = 'teacher')
);

person_type在 b_occupation_person 中是多余的但据我所知，这是以声明方式创建此类约束的唯一选择。

由于外键和检查约束，不能在 b_occupation_person 中插入除教师之外的任何其他内容。 .

但同样:这只有在你能真正区分教师和学生的情况下才有效(并且如果教师不能是学生)。

如果你需要一个人当老师 和 学生(并且您没有“person_type”)，您可能会考虑 teacher仅引用人员表的表:

create table person 
(
   id integer not null primary key, 
   name varchar(100)
);

create table teacher 
(
  person_id integer not null primary key,
  foreign key (person_id) references person (id)
);

create table b_occupation_person
(
  occupation_id integer not null,
  teacher_id integer not null,
  foreign key (teacher_id) references teacher (person_id)
);

这样做的缺点是需要插入两次作为老师的人(一次插入人，一次插入老师)。

在 PostgreSQL 中，您可以利用表继承并将教师定义为从人继承。因此，任何插入到教师中的操作也会自动创建一个人(因此您不需要两次插入这样的人)。