Re: adding a constraint to child table that references a column of the parent table that is not part of the primary key
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Fri, 29 Jan 2010 04:14:49 -0800 (PST)
Message-ID: <e8894986-17c0-4e30-9c50-866a0fdfeb92_at_l30g2000yqb.googlegroups.com>
On Jan 29, 12:18 pm, yossarian <yossaria..._at_operamail.com> wrote:
> Hello, Oracle 10.2.0.3 on Linux here.
>
> 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.
Date: Fri, 29 Jan 2010 04:14:49 -0800 (PST)
Message-ID: <e8894986-17c0-4e30-9c50-866a0fdfeb92_at_l30g2000yqb.googlegroups.com>
On Jan 29, 12:18 pm, yossarian <yossaria..._at_operamail.com> wrote:
> Hello, Oracle 10.2.0.3 on Linux here.
>
> 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.
This is not a data requirement, but a functional/business requirement. You should implement it with calls to Packages/Procedures (with appropriate locks) instead of raw INSERT statements or in the application layer.
Because of the read-commited mode, triggers cannot help you here.
(IMHO) Cheers.
Carlos. Received on Fri Jan 29 2010 - 06:14:49 CST