Re: Multi-column constraint behaviour

Bertram Scharpf wrote:
> Hi,
>
>
> please have a look at these introducing statements:
>
> sandbox=# create table q(i integer, t text, primary key (i,t));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "q_pkey" for table "q"
> CREATE TABLE
> sandbox=# create table f(i integer, t text, foreign key (i,t) references q);
> CREATE TABLE
> sandbox=# insert into q (i,t) values (33,'hi');
> INSERT 0 1
> sandbox=# insert into f (i,t) values (34,'hi');
> ERROR: insert or update on table "f" violates foreign key constraint "f_i_fkey"
> DETAIL: Key (i,t)=(34,hi) is not present in table "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.

Null values are not required to be matched on MATCH SIMPLE foreign keys
(which are the default). If you declare it to be MATCH FULL, it will be
rejected:

alvherre=# drop table f;
DROP TABLE
alvherre=# create table f(i integer, t text, foreign key (i,t) references q match full);
CREATE TABLE
alvherre=# insert into f (i,t) values (34,null);
ERROR: insert or update on table "f" violates foreign key constraint "f_i_fkey"
DETALLE: MATCH FULL does not allow mixing of null and nonnull key values.

This seems, hum, dangerous, but I guess this is the way the spec defines
the behavior. (No, I didn't check.)

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

One way would be to declare the referencing column as NOT NULL. Another
is using MATCH FULL.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support