Thread: FOREIGN KEY: MATCH FULL

Hi,

What does it mean the MATCH FULL parameter in a foreign key specfication
?

It seems it is only valid for a multi-column foreign key: What's a
multi-column foreign key ?

I've tried to search this in Bruce's book but I have a lot of problems
to connect to it at   http://www.postgresql.org/docs/awbook.html

Thanks,

Gabi :-)

On Wed, 5 Sep 2001, Gabriel Fernandez wrote:

> Hi,
>
> What does it mean the MATCH FULL parameter in a foreign key specfication
> ?
>
> It seems it is only valid for a multi-column foreign key: What's a
> multi-column foreign key ?

It's only different on multi-column fks, it's valid but uninteresting
on singles.  A multi column foreign key is something like the one
in the following:
create table foo (
 a int,
 b int,
 foreign key (a,b) references bar
);
The key that's being checked is the combination of a and b.

For the unspecified match type, if either is NULL, the constraint
is passed.  For MATCH FULL, either both must be non-NULL and match
in the other table or both must be null for the constraint to pass.

Gabriel Fernandez wrote:
  >Hi,
  >
  >What does it mean the MATCH FULL parameter in a foreign key specfication
  >?
  >
  >It seems it is only valid for a multi-column foreign key: What's a
  >multi-column foreign key ?

Example:

  CREATE TABLE c (
     col1    VARCHAR(10),
     col2    INT,
     ...,
     PRIMARY key (col1, col2)
  );

  CREATE TABLE d (
     x       CHAR(2)        PRIMARY KEY,
     col1    VARCHAR(10),
     col2    INT,
     ...,
     FOREIGN KEY (col1,col2) REFERENCES c(col1,col2)   --  multi-column foreign key
  );


SQL99:
If MATCH FULL or MATCH PARTIAL is specified for a referential constraint and if
the referencing table has only one column specified in <referential constraint
definition> for that referential constraint, or if the referencing table has
more than one specified column for that <referential constraint definition>,
but none of those columns is nullable, then the effect is the same as if no
<match option> were specified.

...

A referential constraint is satisfied if one of the following conditions is
true, depending on the <match option> specified in the <referential
constraint definition>:

- If no <match type> was specified then, for each row R1 of the referencing
  table, either at least one of the values of the referencing columns in R1
  shall be a null value, or the value of each referencing column in R1 shall
  be equal to the value of the corresponding referenced column in some row of
  the referenced table.

- If MATCH FULL was specified then, for each row R1 of the referencing table,
  either the value of every referencing column in R1 shall be a null value, or
  the value of every referencing column in R1 shall not be null and there shall
  be some row R2 of the referenced table such that the value of each referencing
  column in R1 is equal to the value of the corresponding referenced column in
  R2.

- If MATCH PARTIAL was specified then, for each row R1 of the referencing table,
  there shall be some row R2 of the referenced table such that the value of each
  referencing column in R1 is either null or is equal to the value of the
  corresponding referenced column in R2.

The referencing table may be the same table as the referenced table.



--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Behold, I stand at the door, and knock; if any man
      hear my voice, and open the door, I will come in to
      him, and will sup with him, and he with me."
                                   Revelation 3:20