RE: Problem with subquery in CHECK constraint.

> -----Original Message-----
> From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On 
> Behalf Of Niall Smart
> 
> 
> 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...
>

Doesn't the constraint mean thatfor any row in table CUST,there *always* exist some rows inthe table XREF such that
satisfiesXREF_GROUP='CUST_TYPE'AND XREF_CD=TYPE_CD ?
 
If all such rows are deleted from the table XREF,above condition
isn't satisfied any longer. So isn't the constraint for the table XREF
either ?
Regards.

Hiroshi Inoue
Inoue@tpf.co.jp