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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Lola Lee <lola (AT) his (DOT) com> writes:
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?

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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


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



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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match