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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org