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

Bertram Scharpf <lists@bertram-scharpf.de> writes:

Is there a deeper reason why the foreign key allows not
referenced non-null values

The SQL spec says so. Use MATCH FULL to get the behavior you want.

regards, tom lane

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.

Hi,

Am Dienstag, 16. Jan 2007, 15:51:58 -0500 schrieb Tom Lane:

Bertram Scharpf <lists@bertram-scharpf.de> writes:

Is there a deeper reason why the foreign key allows not
referenced non-null values

The SQL spec says so. Use MATCH FULL to get the behavior you want.

Ah, I should have seen that in the documentation. Sorry for
the noise.

Thanks to all,

Bertram

--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de