UNIQUE constraint matching given keys for referenced table "employee" not found

On Thu, 8 Aug 2002, Jeff Eckermann wrote:

Thought I understood this, but evidently not...
couldn't find anything in the docs or archives that
helped me, either.

Trying to add a new table, then referencing a number
of other tables to a field in the new table. Table
definition:

create table "employee" (
"emp_id" integer default
nextval('employee_emp_id_key'::text) not null,
"emp_num" text,
...
constraint "employee_pkey" primary key
("emp_id"),
constraint "emp_num_key" unique (emp_num)
);

personnel_benefits=# \i employees.dump
You are now connected as new user jeck.
psql:employees.dump:31: NOTICE: CREATE TABLE/PRIMARY
KEY will create implicit index 'employee_pkey' for
table 'employee'
psql:employees.dump:31: NOTICE: CREATE TABLE/UNIQUE
will create implicit index 'emp_num_key' for table
'employee'
CREATE
personnel_benefits=# alter table emp_insurance add
constraint ei_emp_fk foreign key (emp_no) references
employee (emp_no);

/\
||
You've got a typo in your alter table line. Should be emp_num from the table
definition you've given above.

NOTICE: ALTER TABLE ... ADD CONSTRAINT will create
implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for
referenced table "employee" not found
personnel_benefits=#

Then hopefully this error message will magically disappear.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

Ouch: end of a long day :-)

Seems kind of odd that PostgreSQL should care more
about the non-existence of the unique constraint on a
field than about the non-existence of that field
itself. Not the only place where less-than-optimal
error messages may be encountered, however.

Thanks for your help.

--- "Nigel J. Andrews" <nandrews@investsystems.co.uk>
wrote:

On Thu, 8 Aug 2002, Jeff Eckermann wrote:

Thought I understood this, but evidently not...
couldn't find anything in the docs or archives

that

helped me, either.

Trying to add a new table, then referencing a

number

of other tables to a field in the new table.

Table

definition:

create table "employee" (
"emp_id" integer default
nextval('employee_emp_id_key'::text) not null,
"emp_num" text,
...
constraint "employee_pkey" primary key
("emp_id"),
constraint "emp_num_key" unique (emp_num)
);

personnel_benefits=# \i employees.dump
You are now connected as new user jeck.
psql:employees.dump:31: NOTICE: CREATE

TABLE/PRIMARY

KEY will create implicit index 'employee_pkey' for
table 'employee'
psql:employees.dump:31: NOTICE: CREATE

TABLE/UNIQUE

will create implicit index 'emp_num_key' for table
'employee'
CREATE
personnel_benefits=# alter table emp_insurance add
constraint ei_emp_fk foreign key (emp_no)

references

employee (emp_no);

/\
||
You've got a typo in your alter table line. Should
be emp_num from the table
definition you've given above.

NOTICE: ALTER TABLE ... ADD CONSTRAINT will

create

implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for
referenced table "employee" not found
personnel_benefits=#

Then hopefully this error message will magically
disappear.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to

majordomo@postgresql.org

__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:

Seems kind of odd that PostgreSQL should care more
about the non-existence of the unique constraint on a
field than about the non-existence of that field
itself.

Yeah, that's irritated me too. It doesn't look quite trivial to fix
though, since you can't just check that the fields exist --- they may
not exist, yet. Consider a self-referential table:

create table tree_nodes (id int primary key,
parent int references tree_nodes(id),
...);

There are double code paths in all of the analyze.c code for foreign
keys to handle both the already-exists and the will-create-it case.
Rearranging things to check column existence before existence of the
constraint thus looks a bit tricky.

But feel free to send in a patch ;-)

regards, tom lane