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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
