468,103 Members | 1,351 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

Can't insert date in field with foreign key

Hello list,

I'm a postgres-newbie with a maybe silly question.
I'm working on a SuSE-Linux 9.1 with PostgreSQL 7.4.6 with PostGIS 0.9.

My problem is, that I can't insert data into a table because I get the
error, that there is no corresponding value in the referenced field.
But when I query the referenced table with the value, I get a result.
Both fields are of the same type and length (varchar(10)).

1st Table:

CREATE TABLE "T_OEPNV_HST"
(
"HST_ID" serial,
"HST_NR" varchar(10),
"HST_NAME" varchar(256),
"HST_COORD" public.geometry,
CONSTRAINT "T_OEPNV_HST_pkey" PRIMARY KEY ("HST_ID"),
CONSTRAINT "T_OEPNV_HST_unr" UNIQUE ("HST_NR"),
CONSTRAINT "enforce_geotype_HST_COORD" CHECK geometrytype("HST_COORD")
= 'POINT'::text OR "HST_COORD" IS NULL,
CONSTRAINT "enforce_srid_HST_COORD" CHECK srid("HST_COORD") = 31467
) WITH OIDS;

2nd Table:

CREATE TABLE "T_OEPNV_HST_LIN" (
"HST_LIN_ID" serial primary key,
"HST_NR" varchar(10) references "T_OEPNV_HST" on update cascade,
"LIN_U_NAME" varchar(255) references "T_OEPNV_UNTERNEHMEN" ("U_NAME")
on update cascade,
"LIN_KAT" varchar(50) NOT NULL,
"LIN_NAME_O" varchar(50) NOT NULL,
"LIN_NAME_I" varchar(50) NOT NULL
);

The problem is related to the fields "HST_NR" in the two tables.
For Example:
insert into "T_OEPNV_HST_LIN" ("HST_NR", "LIN_U_NAME", "LIN_KAT",
"LIN_NAME_O", "LIN_NAME_I") values ('10001', 'Stadtwerke Marburg GmbH',
'Bus', 'C', '11103');
does not work while
select * from "T_OEPNV_HST" where "HST_NR"='10001';
returns
HST_ID | HST_NR | HST_NAME | HST_COORD
--------+--------+----------+------------------------------------------
23555 | 10001 | Afföller | SRID=31467;POINT(3483856.148 5632168.48)
(1 Zeile)
So, I can't understand, why I get the error, that I can't insert the
data into "T_OEPNV_HST_LIN" because there is no HAST_NR='10001' in
"T_OEPNV_HST".

I hope, someone can help...
Cheers,
Bjoern

--
momatec GmbH
www.momatec.de

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
3 5015
On Thu, Nov 11, 2004 at 08:13:16AM +0100, Bjoern Platzen wrote:
insert into "T_OEPNV_HST_LIN" ("HST_NR", "LIN_U_NAME", "LIN_KAT",
"LIN_NAME_O", "LIN_NAME_I") values ('10001', 'Stadtwerke Marburg GmbH',
'Bus', 'C', '11103');
does not work while
select * from "T_OEPNV_HST" where "HST_NR"='10001';
returns
HST_ID | HST_NR | HST_NAME | HST_COORD
--------+--------+----------+------------------------------------------
23555 | 10001 | Aff?ller | SRID=31467;POINT(3483856.148 5632168.48)
(1 Zeile)


The foreign key constraint on T_OEPNV_HST_LIN.HST_NR references the
wrong field in T_OEPNV_HST. Since you didn't specify a field, it's
referencing the primary key HST_ID instead of HST_NR.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2
=?iso-8859-1?Q?Bj=F6rn_Platzen?= <bj************@momatec.de> writes:
CREATE TABLE "T_OEPNV_HST"
(
"HST_ID" serial,
"HST_NR" varchar(10),
...
CONSTRAINT "T_OEPNV_HST_pkey" PRIMARY KEY ("HST_ID"),
CONSTRAINT "T_OEPNV_HST_unr" UNIQUE ("HST_NR"),
...
CREATE TABLE "T_OEPNV_HST_LIN" (
"HST_NR" varchar(10) references "T_OEPNV_HST" on update cascade,
); So, I can't understand, why I get the error, that I can't insert the
data into "T_OEPNV_HST_LIN" because there is no HST_NR='10001' in
"T_OEPNV_HST".


Since you wrote the REFERENCES clause without mentioning any particular
column name, it defaults to referencing the primary key of
"T_OEPNV_HST", that is, "HST_ID".

When I try the example I get

ERROR: insert or update on table "T_OEPNV_HST_LIN" violates foreign key constraint "T_OEPNV_HST_LIN_HST_NR_fkey"
DETAIL: Key (HST_NR)=(10001) is not present in table "T_OEPNV_HST".

The error message is mentioning the referencing column not the
referenced column. I recall that we decided this was less confusing
than the other choice, but I'm not sure why we thought that.
Particularly now that the FK constraint name includes the referencing
column name by default, I wonder if we ought to switch.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
From: "Björn Platzen" <bj************@momatec.de>
"HST_NR" varchar(10) references "T_OEPNV_HST" on update cascade,


try:
"HST_NR" varchar(10) references T_OEPNV_HST(HST_NR) on update cascade,

gnari

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Victor M. | last post: by
2 posts views Thread by kungfukittens | last post: by
1 post views Thread by mstery | last post: by
3 posts views Thread by 4partee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.