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:25:44 -0800 (PST)
Message-ID: <3f458886-4dbd-41c7-b0fc-400056fd865c_at_o9g2000yqa.googlegroups.com>



On Jan 29, 1:14 pm, Carlos <miotromailcar..._at_netscape.net> wrote:
> 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.

Another (convoluted) solution would be the use of denormalized ('inteligent') pid's and a unique function based index on the child table:

pid's for type 1: 11,12,13, etc...
pid's for type 2: 21,22,23, etc...

create a unique FBI on (mod(pid,10), date)

Cheers.

Carlos. Received on Fri Jan 29 2010 - 06:25:44 CST

Original text of this message