Re: Problem with subquery in CHECK constraint. - Mailing list pgsql-sql

From Stephan Szabo
Subject Re: Problem with subquery in CHECK constraint.
Date
Msg-id 004d01bfd175$7d9af7b0$0c64010a@kick.com
Whole thread Raw
In response to RE: Problem with subquery in CHECK constraint.  ("Hiroshi Inoue" <Inoue@tpf.co.jp>)
List pgsql-sql
> CONSTRAINT TYPE_CD_OK CHECK (
> EXISTS (SELECT 1 FROM XREF WHERE
> XREF_GROUP = 'CUST_TYPE' AND
> XREF_CD = TYPE_CD)
> )
>
>
> > There seems to be more serious problems.
> > 1) The constraint is not only for the defined table but also for
referenced
> >     tables in the subquery.
>
> I don't understand what you mean -- the constraint only
> constrains 1 column in one table...

Think of this sequence:

insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '1');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '2');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '3');
insert into cust (cust_id, name, type_cd) values (1, 'a', '1');
insert into cust (cust_id, name, type_cd) values (2, 'b', '2');
delete from xref where xref_cd='1';

I believe that technically the delete should fail because it breaks the
type_cd_ok constraint, but right now, we wouldn't be even checking
that constraint to notice that we're breaking it.





pgsql-sql by date:

Previous
From: "Hiroshi Inoue"
Date:
Subject: RE: Problem with subquery in CHECK constraint.
Next
From: "Ed"
Date:
Subject: Sum of datetime différence...