adding a constraint to child table that references a column of the parent table that is not part of the primary key

I have two tables, P (parent table) and D (data table):

hr_at_> create table p ( pid integer primary key, ptype varchar2(1) not null, pname varchar2(80) not null ) ;

Table created.

hr_at_> create table d ( pid integer not null, ddate date not null, dstuff varchar2(80), primary key (pid,ddate), foreign key (pid) references p ) ;

Table created.

I would like to add a constraint that prevents to insert two parents of the same type in the same date. For example:

hr_at_> insert into p values (1,'A','1A');

1 row created.

hr_at_> insert into p values (2,'B','2B');

1 row created.

hr_at_> insert into p values (3,'B','3B');

1 row created.

hr_at_> insert into d values (1,date'2010-01-01','ldldldl');

1 row created.

hr_at_> insert into d values (2,date'2010-01-01','kdkdkdk');

1 row created.

hr_at_> insert into d values (3,date'2010-01-01','kasdflkddd');

I wish to reject this last insert because I already have a row of type B  in date 2010-01-01.

This is impossible to implement neither with standard constraints nor with triggers (because of the "table is mutating..." error).

Any suggestion?

Thank you, Y. Received on Fri Jan 29 2010 - 05:18:26 CST