X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E11E0D1B187
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  6 Jun 2004 19:37:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 31340-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  6 Jun 2004 22:37:44 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id EED34D1B182
	for <pgsql-sql@postgresql.org>; Sun,  6 Jun 2004 19:37:40 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i56Mbg7p021441;
	Sun, 6 Jun 2004 18:37:43 -0400 (EDT)
To: nzanella@cs.mun.ca (Neil Zanella)
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL DDL: FOREIGN KEY construct and field mapping: unexpected
	behavior
In-reply-to: <b68d2f19.0406060019.2daa1082@posting.google.com> 
References: <b68d2f19.0406060019.2daa1082@posting.google.com>
Comments: In-reply-to nzanella@cs.mun.ca (Neil Zanella)
	message dated "06 Jun 2004 01:19:50 -0700"
Date: Sun, 06 Jun 2004 18:37:42 -0400
Message-ID: <21440.1086561462@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0
	tests=LINES_OF_YELLING
X-Spam-Level: 
X-Archive-Number: 200406/47
X-Sequence-Number: 17992

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 that unique 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 table and columns>. The i-th col-
            umn identified in the <referencing columns> corresponds to the
            i-th column identified in the <referenced table and columns>.
            The data type of each referencing column shall be the same as
            the data type of the corresponding referenced column.

Nothing there about "try to match by name".

			regards, tom lane