Oracle SQL，寻找有关约束的想法

Question

我有一个包含班级、教师和领导者行的表，

班级和教师是数字，领导者是布尔值（实际上只是一个 Char(1)）

该表表示教师和班级之间的关系。

一个班级可以有多个老师，或者一个老师可以有多个班级。

我需要进行约束或检查，以便对于每个不同的类，它出现的一个且只有一个行必须将 Leader 设为 true。

例如。

Teacher |  Class | Leader
   1    |    1   |  True
   2    |    1   |  False
   2    |    2   |  True

会被接受，因为每个不同的类都有一行，其中它的 Leader 值为 true，但不超过一行。

如果有人能提出如何将其表示为约束的想法，我将不胜感激。

Answer 1

创建一个基于函数的索引来强制执行约束的“不超过一个领导者”部分是很容易的。

CREATE UNIQUE INDEX one_leader_per_class
    ON your_table_name( (CASE WHEN leader = 'Y' 
                              THEN class
                              ELSE null
                          END) );

不幸的是，创建一个声明性约束来强制执行每个类都有一个领导者的要求并不容易。我知道执行此操作的唯一声明性方法是创建一个按类聚合数据的物化视图，将该物化视图设置为 REFRESH FAST 并在物化视图上创建一个约束，以确保物化视图中的 NUM_LEADERS 列始终为 1。这要求您创建适当的物化视图日志，这会增加表上 DML 的开销。这也意味着在您提交之前不会违反约束，如果您的应用程序没有编写为假设提交可能会失败，那么这可能会出现问题。

Answer 2

考虑将 is_Leader（布尔值）列更改为 leadership_rank（整数），对（教师、班级领导力_rank）创建复合唯一约束。然后稍微改变语义，以便每个老师得到一个唯一的随机/递增/有意义的整数值。然后，班级的宣布领导者是老师，其中leadership_rank = 1（或者可能是该组的最低排名，以使事情更加灵活），也许使用视图。

Answer 3

另一种方法是创建一个名为 Leader 的表，其中包含：

CREATE TABLE Leader
( Class
, Teacher
, PRIMARY KEY (Class)
, FOREIGN KEY (Class, Teacher)
    REFERENCES ClassTeacher (Class, Teacher)
) ;

并删除 ClassTeacher.Leader 列。

Answer 4

考虑以不同的方式对关系进行建模，而不是添加约束。

由于 class 和 class 之间存在一对一的关系。领导者
我认为实现您想要的结果的最简单方法是将领导者 (class_teacher_id) 与班级记录一起存储。您需要将唯一的代理键 (class_teacher_id) 添加到多对多表 (class_teacher)，然后可以删除领导者布尔列。

如果可能有一个没有领导者的类，那么只需允许 learderid 可为空
当然，不要忘记创建外键约束以强制 class.leaderid 和新的 class_teacher.class_teacher_id 之间的引用完整性。

Answer 5

要强制要求每个班级都有一个领导者，您可以使用触发器

CREATE OR REPLACE TRIGGER tc
AFTER INSERT OR UPDATE OR DELETE
ON table_name
DECLARE
 v_res NUMBER;
BEGIN
  select count(*) 
  into v_res 
  from (
        select class from table_name
        minus
        select class from table_name where leader = 'Y'
        )
  ;

   if v_res > 0 
   then 
    raise_application_error(-20000, 'each class must have a leader'); 
   end if; 
END;
/