Oracle（或任何关系型）数据模型问题。父母有固定数量的孩子吗？

Question

这是我多次遇到的一个特殊问题， 但我从未真正找到解决这个（看似）简单问题的简单方法。

如何确保给定的父母有固定数量的孩子？

1）示例。

您如何确保某个班级只有 50 名学生注册……？

create table class(
  class_id number primary key,
  class_name varchar2(50),
  class_attributes varchar2(50)
);

create table student(
    student_id number primary key,
    student_name varchar2(50),
    student_attributes varchar2(50)
);

create table class_student_asc(
    class_id number,
    student_id number,
    other_attributes varchar2(50),
    constraint pk_class_student_asc primary key (class_id,student_id),
    constraint fk_class_id foreign key (class_id) references class(class_id),
    constraint fk_student_id foreign key (student_id) references student(student_id)
);

这些是我所知道的实现。 让我知道您更喜欢哪一个，以及是否有更简单的方法来实现这一目标。

a)

使用子表 (class_student_asc) 上的触发器来实现它。

在插入前、更新触发器中查询同一个表以获取计数。 由于这会产生变异表错误，因此将其分为两个不同的语句级别 触发器（语句前和语句后）来实现结果。

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

b)

在类表中包含一个计数变量，并在将记录插入子表之前锁定父记录以进行更新。

所以，类似......

create table class(
     class_id number primary key,
     class_name varchar2(50),
     class_attributes varchar2(50),
     class_count INTEGER,
     constraint chk_count_Students check (class_count <=5)
);

而不是暴露表 class_student_asc 进行插入等等...... 编写一个过程，然后在所有应用程序中使用它。

procedure assign_new_student( 
        i_student_id number,
        i_class_id number)
   is 
   begin
       select class_count
         from class
         where class_id = i_class_id
           for update ; -- or for update nowait, if you want the other concurrent transaction to fail..

   insert into class_student_asc(
       class_id, student_id) 
     values (i_class_id,i_student_id);

   update class
      set class_count = class_count + 1
      where class_id = i_class_id;

   commit;
end assign_new_student;

c)

当然，也有一些情况，例如用户拥有两个电子邮件地址。 在这种情况下，电子邮件地址本身没有任何属性 该表可以很简单，

create table user_table
          (
            user_id number,
            user_name varchar2(50),
            user_email_primary varchar2(50),
            user_email_secondary varchar2(50)
          );

但是，我们不能为上述问题扩展相同的方法......因为列数和约束检查会减慢插入和更新速度。另外，这意味着我们每次更改规则时都需要添加一个新列。

请指教。

Answer 1

对于 Oracle 来说，请考虑这种方法。

创建一个物化视图，汇总每个班级的学生人数。让 mview 在提交时刷新，并向 mview 添加一个约束，禁止每个班级的学生人数超过 50 人。

此代码演示了如何使用提交 mview 上的快速刷新来强制执行学生计数限制，

insert into class(class_id, class_name) values (1, 'Constraints 101');
insert into class(class_id, class_name) values (2, 'Constraints 201');
insert into student(student_id, student_name) values(1, 'Alice');
insert into student(student_id, student_name) values(2, 'Bob');
insert into student(student_id, student_name) values(3, 'Carlos');

create materialized view log on class_student_asc with primary key, rowid, sequence including new values;

create materialized view class_limit refresh fast on commit as
  select class_id, count(*) count from class_student_asc group by class_id;

alter table class_limit add constraint class_limit_max check(count <= 2);

insert into class_student_asc(class_id, student_id) values(1, 1);
insert into class_student_asc(class_id, student_id) values(1, 2);
insert into class_student_asc(class_id, student_id) values(1, 3);

该约束将在提交事务时违反，而不是在将第三个学生添加到班级时违反。这可能会对您的应用程序代码产生影响。 SQL Developer 无法显示该错误，但 sql*plus 会显示该错误。

Answer 2

我可以想到几种方法：

1。触发器

在表上有一个 INSERT 触发器，用于检查 INSERT 并为您进行验证。

2.一对一关系

假设您希望一位父母只拥有两个孩子。创建两个一对一的关系。

Answer 3

另一个问题有类似的要求，您可以在“count”列上使用 CHECK 约束和 UNIQUE 约束的组合进行约束：

如何快速从Oracle中选择数据