By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Can't insert date in field with foreign key

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
=?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

P: n/a
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.