views:

88

answers:

3

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.

A: 

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.

Raj More
1) is the approach that causes mutating table error (or) if you use some code to avoid it, it is kind of ... complex?2) unless the children have some identifiable attributes, (say..) primary and secondary email, how would you implement this? for every new insert,you'd have to scan through all the tables to see if any of them do not have a child. Also, If you have to get all the children for a given parent, the uqery will have to join all the child tables?
Rajesh
@rkumar: (1) would give you a mutating table exception if the trigger is a row trigger. If, however, you make it a table trigger you can do this. The question is how well (or badly) such a trigger would perform.
Bob Jarvis
+3  A: 

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.

alt text

Janek Bogucki
Very neat...although, we'd be refreshing the entire MV (for all classes and students) for a every new student...
Rajesh
IIRC, with the new values clause on the materialized view log the refresh would be incremental. I have not checked this.
Janek Bogucki
@rkumar: Keep in mind that not all databases support materialized views - MySQL and PostgreSQL don't
OMG Ponies
A: 

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:

http://stackoverflow.com/questions/3345132/how-to-fastly-select-data-from-oracle/3350070#3350070

Jeffrey Kemp