没有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表中，有两行： Student和Teacher。 B_Occupation_Person绑定表允许我为所有人分配职业，而B_Course_Person绑定表允许我将教师与课程关联起来。

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

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

我的第二个想法是在B_Course_Person表中添加一个Occupation列，但这样会导致数据冗余...

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

谢谢，

- Rodolphe

---

你花了多少时间创建这些整洁的ASCII表格来回答这个问题？！！ - Mark Byers

@MarkByers：它们不是ASCII码。 - hmakholm left over Monica

@MarkByers，我不确定是否使用的是这个工具，但这个神奇的工具最近在meta上成为了一个 [feature-request] 的话题。 - Ben

@MarkByers 我使用了这个工具，我是通过谷歌搜索“stackoverflow format sql table”找到的。 - Rodolphe

@Andomar 我会记住这个建议，但我倾向于避免使用存储过程，因为它不容易调试。 - Rodolphe

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')
);

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

由于外键和检查约束，除了教师之外，无法插入其他任何内容到 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)
);

这样做的缺点是需要将一个人作为教师插入两次（一次插入到person中，一次插入到teacher中）。

在PostgreSQL中，您可以利用表继承并定义teacher从person继承。因此，任何对teacher的插入都会自动创建一个person（因此您不需要将这样的人插入两次）。

- user330315

---

将 person_type 添加为多对多关系的属性会违反正常形式。不确定这是否是一个好主意。 - Andomar

@Andomar：一般来说，在多对多关系中添加列并不一定违反3NF，因为您可能实际上想要有描述关系类型的属性。但是您是正确的，我的示例确实违反了3NF，但这是由于“person”表中的PK（因为其中并非所有属性都依赖于PK）。但在这种情况下，我没有看到通过静态声明的约束（而不是触发器）强制执行此要求的不同方法。 - user330315

同意。我只是认为这种程度的弯曲静态约束是一个坏主意，业务逻辑应该在其他地方执行。 - Andomar

Answer 2

你的问题代表了一种业务逻辑。你不仅需要确保在插入时 b_course_person 只包含教师，还需要确保数据的一致性 - 也就是说，如果一个人停止成为教师，b_course_person 中的数据会发生什么变化？

因此，你应该在代码中实现这个逻辑；可以将其作为存储过程来实现，表示你与数据库的接口，并确保所有数据更改都会产生一组一致、有效的数据，或者在应用程序的业务逻辑层中进一步处理。

- podiluska