From: nzanella@cs.mun.ca
To: pgsql-sql@postgresql.org
Subject: SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior
Date: 6 Jun 2004 01:19:50 -0700
Message-ID: <b68d2f19.0406060019.2daa1082@posting.google.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nzanella@cs.mun.ca
Subject: Re: SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior
In-Reply-To: <b68d2f19.0406060019.2daa1082@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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