pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior
2+ messages / 2 participants
[nested] [flat]
* SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior
@ 2004-06-06 08:19 Neil Zanella <nzanella@cs.mun.ca>
0 siblings, 1 reply; 2+ messages in thread
From: nzanella@cs.mun.ca @ 2004-06-06 08:19 UTC (permalink / raw)
To: pgsql-sql
Hello,
Consider the following code run under PostgreSQL 7.3.4:
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
);
INSERT INTO X (A, B) VALUES (1, 2);
INSERT INTO Y (A, B, C) VALUES (1, 2, 3);
The second insert causes the database server to report the following
error:
ERROR: $1 referential integrity violation
- key referenced from y not found in x
Upon examining the interpretation of PostgreSQL's
REFERENCES clause I find the following:
foodb=# \d x
Table "public.x"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | integer | not null
Indexes: x_pkey primary key btree (a, b)
foodb=# \d y
Table "public.y"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer | not null
Indexes: y_pkey primary key btree (c)
Foreign Key constraints: $1 FOREIGN KEY (b, a)
REFERENCES x(a, b) ON UPDATE NO ACTION ON DELETE NO ACTION
It is as though PostgreSQL, instead of matching names, associated
field A in table Y with field B in table X
and
field B in table Y with field A in table X
whereas I was expecting the database server to match the names as in:
field A in table Y with field A in table X
and
field B in table Y with field B in table X
I wonder what the SQL standard has to say on this one and how the
REFERENCES clause with no field names on the right hand side
really works in spite of the unexpected results produced by
this very simple example...
Thanks,
Neil
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior
@ 2004-06-06 22:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Neil Zanella <nzanella@cs.mun.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-06-06 22:37 UTC (permalink / raw)
To: nzanella@cs.mun.ca; +Cc: 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 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-06-06 22:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-06 08:19 SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior Neil Zanella <nzanella@cs.mun.ca>
2004-06-06 22:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox