Обсуждение: 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
Bertram Scharpf <lists@bertram-scharpf.de> writes:
> Is there a deeper reason why the foreign key allows not
> referenced non-null values
The SQL spec says so. Use MATCH FULL to get the behavior you want.
regards, tom lane
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.
Bertram Scharpf wrote: > 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. Null values are not required to be matched on MATCH SIMPLE foreign keys (which are the default). If you declare it to be MATCH FULL, it will be rejected: alvherre=# drop table f; DROP TABLE alvherre=# create table f(i integer, t text, foreign key (i,t) references q match full); CREATE TABLE alvherre=# insert into f (i,t) values (34,null); ERROR: insert or update on table "f" violates foreign key constraint "f_i_fkey" DETALLE: MATCH FULL does not allow mixing of null and nonnull key values. This seems, hum, dangerous, but I guess this is the way the spec defines the behavior. (No, I didn't check.) > 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? One way would be to declare the referencing column as NOT NULL. Another is using MATCH FULL. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hi, Am Dienstag, 16. Jan 2007, 15:51:58 -0500 schrieb Tom Lane: > Bertram Scharpf <lists@bertram-scharpf.de> writes: > > Is there a deeper reason why the foreign key allows not > > referenced non-null values > > The SQL spec says so. Use MATCH FULL to get the behavior you want. Ah, I should have seen that in the documentation. Sorry for the noise. Thanks to all, Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de