> 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.