Re: Foreign Key Problem

Lola Lee <lola@his.com> writes:
> CREATE TABLE needles (
>         needles_id          SERIAL,
>         ...
>         PRIMARY KEY (needles_id),
>         FOREIGN KEY (needles_id)
>                               REFERENCES accessory,

This seems a fairly bizarre design.  I've never seen a table in which a
primary key is simultaneously a foreign key to some other table --- you
might as well merge the two tables together.  And if the primary key is
generated as a SERIAL sequence (which essentially means you abdicate
responsibility for choosing its values) how could it be a valid
reference to pre-existing entries in another table?

What are you trying to accomplish, exactly?

> INSERT INTO "needles" ("needles_id", "needle_style_id", "needle_mm",
> "needle_length", "needle_lud") VALUES
> (nextval('public.needles_needles_id_seq'::text), '1', '2.25', '24"',
> NULL)
> ERROR:  insert or update on table "needles" violates foreign key constraint "$1"

> What could be the problem?

See above.  You generated a value for needles_id that doesn't match any
row in the accessory table.

            regards, tom lane