Обсуждение: 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