Re: Foreign Key Problem

Lola Lee <lola(at)his(dot)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