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: Sat, 30 Jan 2010 12:21:35 -0800 (PST)
Message-ID: <cf8ce8ba-8ef1-4ffb-9fec-22c8d27523f0_at_b10g2000yqa.googlegroups.com>
On Jan 29, 3:53 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 29-1-2010 12:18, yossarian schreef:
>
>
>
> > 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.
>
> What happens if in table P column ptype is updated?
> All details have to be validated again. I think your datamodel is wrong.
>
> Shakespeare
Date: Sat, 30 Jan 2010 12:21:35 -0800 (PST)
Message-ID: <cf8ce8ba-8ef1-4ffb-9fec-22c8d27523f0_at_b10g2000yqa.googlegroups.com>
On Jan 29, 3:53 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 29-1-2010 12:18, yossarian schreef:
>
>
>
> > 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.
>
> What happens if in table P column ptype is updated?
> All details have to be validated again. I think your datamodel is wrong.
>
> Shakespeare
>>"I think your datamodel is wrong."
Me too ;-)
'Intelligent' PK's would do the job (but its so ugly...)
Cheers.
Carlos. Received on Sat Jan 30 2010 - 14:21:35 CST