代码之家 › 专栏 › 技术社区 › Rajesh Chamarthi

oracle(或任何关系型)数据模型问题。有固定子女数的父母?

data-modeling plsql oracle sql

Rajesh Chamarthi · 技术社区 · 14 年前

这是我经常遇到的一个问题, 但我从来没有真正找到一个简单的解决这个(看似)简单的问题的方法。

1) 例如。

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

这些是我所知道的实现。

(一)

在子表(class\u student\u asc)上用触发器实现它。

在before insert中查询同一个表,update触发器获取计数。因为这会产生变异表错误,所以这被分为两个不同的语句级别触发器(before语句和after语句)以实现结果。。

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

(二)

所以,有点像。。

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

编写一个过程,然后在所有应用程序中使用它。。

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;

当然,也有这样的情况,比如一个用户有两个电子邮件地址。桌子可以简单到

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

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

请给我建议。

3 回复 | 直到 14 年前

Glorfindel Doug L. 5 年前

对于Oracle,请考虑这种方法。

创建一个物化视图,总结每个类的学生数量。让mview在提交时刷新,并向mview添加一个限制,该限制禁止每个类计数超过50个学生。

这段代码演示了如何使用commit 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确实显示了错误。

Raj More 14 年前

我可以想出几个办法:

1触发器

2一对一关系

Community CDub 8 年前

另一个问题有一个类似的要求,您可以使用CHECK约束和“count”列上的唯一约束的组合来约束它:

How to fastly select data from Oracle