MATCH FULL flawed?
Hi:
I'm trying MATCH FULL and it looks like it doesn't work (does anyone use
it anyway?).
encuentro=> select version();
version
-------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
encuentro=> create table test (a serial, b serial, primary key (a, b));
NOTICE: CREATE TABLE will create implicit sequence 'test_a_seq' for SERIAL column 'test.a'
NOTICE: CREATE TABLE will create implicit sequence 'test_b_seq' for SERIAL column 'test.b'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test_pkey' for table 'test'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_a_key' for table 'test'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_b_key' for table 'test'
CREATE
encuentro=> create table test2 (a integer references test (a) match full, b integer references test (b) match full);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
encuentro=> insert into test2 values (null, 1);
INSERT 37655 1
encuentro=> insert into test2 values (1, null);
INSERT 37656 1
encuentro=> insert into test2 values (null, null);
INSERT 37657 1
encuentro=> insert into test2 values (1, 1);
INSERT 37658 1
But from reading the manual I'd say that only the last two should be
allowed.
What's wrong? Am I missing something?
--
Alvaro Herrera (<alvherre[@]atentus.com>)
"La paz mas desventajosa es mejor que la guerra mas justa"
Alvaro Herrera <alvherre@atentus.com> writes:
I'm trying MATCH FULL and it looks like it doesn't work (does anyone use
it anyway?).
You're not using it right. You specified a separate MATCH FULL
constraint for each column:
encuentro=> create table test2 (a integer references test (a) match full, b integer references test (b) match full);
This is essentially a no-op, since MATCH FULL and MATCH PARTIAL are only
meaningfully different for a multi-column key. What you want is
regression=# create table test2 (a integer, b integer,
regression(# foreign key (a,b) references test(a,b) match full);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
which has the behavior you're after:
regression=# insert into test2 values (null, 1);
ERROR: <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
regression=# insert into test2 values (1, null);
ERROR: <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
regression=# insert into test2 values (null, null);
INSERT 262789 1
regression=# insert into test2 values (1, 1);
INSERT 262790 1
regression=#
regards, tom lane
On Mon, 22 Oct 2001, Tom Lane wrote:
Alvaro Herrera <alvherre@atentus.com> writes:
I'm trying MATCH FULL and it looks like it doesn't work (does anyone use
it anyway?).You're not using it right. You specified a separate MATCH FULL
constraint for each column:
Thank you very much for the clarification. I was actually reading the
docs and couldn't find the right way to do it.
Now that I think about it: it's in the column_constraint part of the
definition! Shouldn't it only be in the table_constraint part? It
doesn't make much sense.
--
Alvaro Herrera (<alvherre[@]atentus.com>)
"La verdad no siempre es bonita, pero el hambre de ella si"
Alvaro Herrera <alvherre@atentus.com> writes:
You're not using it right. You specified a separate MATCH FULL
constraint for each column:
Now that I think about it: it's in the column_constraint part of the
definition! Shouldn't it only be in the table_constraint part? It
doesn't make much sense.
No, it doesn't, but the SQL spec requires us to accept it both places...
regards, tom lane
Alvaro Herrera writes:
Now that I think about it: it's in the column_constraint part of the
definition! Shouldn't it only be in the table_constraint part? It
doesn't make much sense.
"Sense" is something you're going to have to talk to the SQL standards
committee about. ;-) It's standardized, it's implemented, therefore it's
legal and documented. However, you're right in that it's rather useless.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter