Subject Re: Testing a ref constraint violation
Author Adam
--- In, Martin Ă…gren
<martin_gbg_@...> wrote:
> Hi there,
> I want to set up a multi column foreign key from table B (detail) to A
> (master), but it fails due to violation of the primary key in A.
> How would the most straightforward SQL look, if I want to find the
> in table B who's constraint fields are non existent in table A?

The most straightforward is the never use composite keys for foreign
key constraints. Always create a surrogate primary key on your table
and you will never need to worry about that.

select *
from tableB b
where not exists
select *
from tableA a
where b.field1 = a.field1
and b.field2 = a.field2