From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Bertram Scharpf <lists(at)bertram-scharpf(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multi-column constraint behaviour |
Date: | 2007-01-16 20:59:33 |
Message-ID: | 20070116123325.V15097@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Bell | 2007-01-16 21:24:53 | 8.1 vs 8.2.1 view optimization |
Previous Message | Harpreet Dhaliwal | 2007-01-16 20:55:22 | Re: Dynamic loading of Perl Code in Postgres functions |