Creating a table: UNIQUE constraint matching given keys for referenced table "xy" not found

From: Jason Hihn <jhihn(at)paytimepayroll(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Creating a table: UNIQUE constraint matching given keys for referenced table "xy" not found
Date: 2003-08-07 14:11:54
Message-ID: NGBBLHANMLKMHPDGJGAPIEEDCMAA.jhihn@paytimepayroll.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Ok, so I have a table ("xy" that doesn't complain about using
PRIMARY KEY(a INTEGER NOT NULL, y INTEGER NOT NULL)

But I have another table ("xyz") that uses x, y and z, and it tells me:
(using PRIMARY KEY(a INTEGER NOT NULL, y INTEGER NOT NULL, z INTEGER NOT
NULL))
CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "xy" not
found

Envision a table storing defined coordinates in 2d space (x,y) and another
table tracking a set of values (indexed by z). "xy" is used (among other
things) to serve as the master list of available points who can have values.
Z takes on 1 to 12, and another field in "xyz" take the value associated
with it.

The thing is, neither x or y are unique but their combination is. I can't
figure out why it won't take (x,y,z) as a primary key. I have even tried
adding "UNIQUE (x,y)" to the "xy" table definition.

Any help is appreciated.

Appendix:
create table xy( x integer not null, y integer not null, primary key (x,y));
create table xyz( x integer not null references xy(x), y integer not null
references xy(y), z integer not null, primary key (x,y,z));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'xyz_pkey'
for table 'xyz'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "xy" not
found

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno Wolff III 2003-08-07 14:27:24 Re: Creating a table: UNIQUE constraint matching given keys for referenced table "xy" not found
Previous Message Tom Lane 2003-08-07 14:04:13 Re: [PHP] authentication question