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
