Re: Foreign Key Problem

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(at)his(dot)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.