This is a particular problem that I have come across many times, but I have never really found a simple solution to this (seemingly) simple problem.
How to you ensure that a given parent has a fixed number of children?
1) Example.
How do you make sure a given class has only , say, 50 students enrolled..?
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)
);
These are the implementations that I know of. Let me know which one you'd prefer and if there is a simpler way to achieve this.
a)
Implementing it with triggers on the child table (class_student_asc).
Querying the same table in a before insert, update trigger to get the count. Since this gives the mutating table error, this is split into two different statement-level triggers (before-statement and after-statement) to achieve the result..
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936
b)
Include a count variable in the class table and lock the parent record for update before inserting a record ito the child table.
So, something like..
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)
);
and instead of exposing the table class_student_asc for inserts and so on... write a procedure and then use it in all applications..
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)
There are, of course, cases like a user having two email adresses. In such a scenario, the email address itself does not have any attribute and the table could be as simple as
create table user_table
(
user_id number,
user_name varchar2(50),
user_email_primary varchar2(50),
user_email_secondary varchar2(50)
);
However, we cannot extend the same approach for the question above.....as the number of columns and the constraint checks would slow down the inserts and updates . Also, this would mean we'd need a new column added everytime we change the rule.. too.
Please advice.