From: | "Stephan Szabo" <sszabo(at)kick(dot)com> |
---|---|
To: | "Niall Smart" <niall(dot)smart(at)ebeon(dot)com> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Problem with subquery in CHECK constraint. |
Date: | 2000-06-08 18:15:08 |
Message-ID: | 004d01bfd175$7d9af7b0$0c64010a@kick.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ed | 2000-06-08 19:39:58 | Sum of datetime différence... |
Previous Message | Hiroshi Inoue | 2000-06-08 18:01:50 | RE: Problem with subquery in CHECK constraint. |