Re: adding a constraint to child table that references a column of the parent table that is not part of the primary key
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 29 Jan 2010 15:53:49 +0100
Message-ID: <4b62f66d$0$22934$e4fe514c_at_news.xs4all.nl>
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.
Date: Fri, 29 Jan 2010 15:53:49 +0100
Message-ID: <4b62f66d$0$22934$e4fe514c_at_news.xs4all.nl>
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 Received on Fri Jan 29 2010 - 08:53:49 CST