Re: Multi-column constraint behaviour

On Tue, 16 Jan 2007, Bertram Scharpf wrote:

> Hi,
>
>
> please have a look at these introducing statements:
>
>   sandbox=# create table q(i integer, t text, primary key (i,t));
>   sandbox=# create table f(i integer, t text, foreign key (i,t) references q);
>
> Now, this is surprising me:
>
>   sandbox=# insert into f (i,t) values (34,null);
>   INSERT 0 1
>   sandbox=# select * from f;
>    i  | t
>   ----+---
>    34 |
>
> What I expected was that the constraint forces all values to
> be null when there is no referenced value pair. I were bored
> if I had to fix this behaviour with check constraints for
> every occurrence of the columns pair.
>
> Is there a deeper reason why the foreign key allows not
> referenced non-null values or is there an easy way to fix
> the whole behaviour?

You're using the default match type (also known as match simple I think)
for which the rules are that it passes if there are any nulls or all are
non-null and have a matching row. Match full says that either all must be
null or all must be non-null and have a matching row. That's probably more
like what you want.