Multi-column constraint behaviour

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.

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?

Thanks in advance,

Bertram


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