Re: SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior - Mailing list pgsql-sql

nzanella@cs.mun.ca (Neil Zanella) writes:
> CREATE TABLE X (
>   A INT,
>   B INT,
>   PRIMARY KEY (A, B)
> );

> CREATE TABLE Y (
>   A INT,
>   B INT,
>   C INT,
>   PRIMARY KEY (C),
>   FOREIGN KEY (B, A) REFERENCES X
> );

> whereas I was expecting the database server to match the names as in:

Why were you expecting that?  The SQL spec is perfectly clear that the
columns are matched in the order written in the primary key.  For
instance, SQL92 11.8 <referential constraint definition> saith:
        2) Case:           b) If the <referenced table and columns> does not specify a             <reference column
list>,then the table descriptor of the             referenced table shall include a unique constraint that spec-
    ifies PRIMARY KEY. Let referenced columns be the column or             columns identified by the unique columns in
thatunique con-             straint and let referenced column be one such column. The             <referenced table and
columns>shall be considered to implic-             itly specify a <reference column list> that is identical to
  that <unique column list>.
 
        7) The <referencing columns> shall contain the same number of <col-           umn name>s as the <referenced
tableand columns>. The i-th col-           umn identified in the <referencing columns> corresponds to the
i-thcolumn identified in the <referenced table and columns>.           The data type of each referencing column shall
bethe same as           the data type of the corresponding referenced column.
 

Nothing there about "try to match by name".
        regards, tom lane