Обсуждение: UNIQUE constraint matching given keys for referenced table "employee" not found

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,
        "first_name" text not null,
        "middle_name" text,
        "last_name" text not null,
        "suffix" text,
        "salutation" text,
        "ssn" text,
        "sex" text,
        "race_id" text,
        "dob" date,
        "review" text,
        "comment" text,
        "history" text,
        "date_entered" date,
        "marital_status" text,
        "ond3" boolean,
        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);
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=#



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

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