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
