Re: FOREIGN KEY: MATCH FULL - Mailing list pgsql-general
From | Oliver Elphick |
---|---|
Subject | Re: FOREIGN KEY: MATCH FULL |
Date | |
Msg-id | 200109060624.f866OK8s008233@linda.lfix.co.uk Whole thread Raw |
In response to | FOREIGN KEY: MATCH FULL (Gabriel Fernandez <gabi@unica.edu>) |
List | pgsql-general |
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
pgsql-general by date: