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

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

On Thu, Aug 07, 2003 at 10:11:54 -0400,
  Jason Hihn <jhihn@paytimepayroll.com> wrote:
>
> 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

You either need to create a unique index for x and for y for table xy
or you need to use the foreign key clause to use (x,y) as a reference
to table xy.

Jason Hihn <jhihn@paytimepayroll.com> writes:
> 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));
> ERROR:  UNIQUE constraint matching given keys for referenced table "xy" not
> found

I think what you want is a two-column foreign key reference:

create table xyz( x integer not null, y integer not null, z integer not null,
primary key (x,y,z),
foreign key (x,y) references xy(x,y));

            regards, tom lane

*smacks palm against forehead in a I-feel-like-a-dolt manner*
Thank you Bruno and Tom!

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Thursday, August 07, 2003 10:42 AM
> To: Jason Hihn
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Creating a table: UNIQUE constraint matching given
> keys for referenced table "xy" not found
>
>
> Jason Hihn <jhihn@paytimepayroll.com> writes:
> > 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));
> > ERROR:  UNIQUE constraint matching given keys for referenced
> table "xy" not
> > found
>
> I think what you want is a two-column foreign key reference:
>
> create table xyz( x integer not null, y integer not null, z
> integer not null,
> primary key (x,y,z),
> foreign key (x,y) references xy(x,y));
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>