Re: FOREIGN KEY: MATCH FULL

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(dot)Elphick(at)lfix(dot)co(dot)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