Обсуждение: Foreign Key Problem

I'm using PostgreSQL 7.4, and I have two tables, which I created as follows:

CREATE TABLE needlestyle (
        needle_style_id     SERIAL,
        needle_style_desc    varchar(50) NULL,
        needle_style_lud     timestamp NULL,
        PRIMARY KEY (needle_style_id)
);

CREATE TABLE needles (
        needles_id          SERIAL,
        needle_style_id     int NULL,
        needle_mm            decimal(5,2) NULL,
        needle_length        varchar(20) NULL,
        needle_lud           timestamp NULL,
        PRIMARY KEY (needles_id),
        FOREIGN KEY (needles_id)
                              REFERENCES accessory,
        FOREIGN KEY (needle_style_id)
                              REFERENCES needlestyle
);


I filled the needlestyle table with three records.  Then I tried to
run the following insert via phpPgAdmin and got the following error
message:

ERROR:  insert or update on table "needles" violates foreign key constraint "$1"


In statement:
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)

What could be the problem?

--

Lola - mailto:lola@his.com
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
Check out this blog:  http://www.denbeste.nu
I'm in Bowie, MD, USA, halfway between DC and Annapolis.

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

At 10:53 PM -0500 12/26/03, Tom Lane wrote:

>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?


First of all, there's the schema of the whole database up at
<http://www.nwkniterati.com/MovableType/archives/sqlforknitters/000370.html>.
The database creation script is elsewhere in this blog; it was
created originally for MS SQL Server and I adapted the script for use
with PostgreSQL.  The blogger seems to have abandoned this particular
project, so I don't think more information about the database will be
forthcoming any time soon.  I do think this schema seems a bit
complex, but she seems to have good reasons for doing it like she
did.  I changed _key to _id, though, since it makes more sense to me
and this is what I'm used to seeing at work.

I'm adapting this database for personal use, to keep track of my
books, needles, yarn, etc.  Eventually I'll be expanding this
database to keep track of my spinning projects, after I've figured my
way around this schema.  I'm using ColdFusion as the front end with
the database.

Needlestyle and needles do need to be separate.  There are only 4
styles of needles - circular, flex jumpers, single pointed and
doublepointed.  But, there are different sizes and lengths for each
type of the needle.  For instance, one manufacturer will have 16
sizes that have a length of 24 inchess (see
http://www.patternworks.com/PWShopping/needles.asp for example).


>  > 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.


Hmm, that could make sense.  What I've been doing is filling in some
of the tables to see how it all works and to provide test data to use
with the ColdFusion front end that I'm developing.

--

Lola - mailto:lola@his.com
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
Check out this blog:  http://www.denbeste.nu
I'm in Bowie, MD, USA, halfway between DC and Annapolis.