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