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.
