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=65511开发者_运维技巧98119097816936
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.
For Oracle consider this approach.
Create a materialized view summarising the number of students per class. Have the mview refresh on commit and add a constraint to the mview that prohibits a count of more than 50 students per class.
This code demonstrates how to use a fast refresh on commit mview to enforce the student count limit,
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);
The constraint will be violated when the transaction is committed, not when the third student is added to the class. This might make a difference to your application code. SQL Developer fails to display the error but sql*plus does display it.
I can think of a couple of ways:
1. Triggers
Have an INSERT Trigger on the table that checks on INSERT and does the validation for you.
2. One-to-One relationships
Let's say you want one parent to have only two children. Create two one-to-one relationships.
Another question had a similar requirement, which you can constrain using a combination of a CHECK constraint with a UNIQUE constraint on a "count" column:
How to fastly select data from Oracle
精彩评论