Prev: find position of row in set of rows
Next: Download oracle 9iR2
From: yossarian on 29 Jan 2010 06:18
Hello, Oracle 10.2.0.3 on Linux here.

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

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

Table created.

hr@> 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@> insert into p values (1,'A','1A');

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

hr@> 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.
 | 
Pages: 1
Prev: find position of row in set of rows
Next: Download oracle 9iR2