Can't insert date in field with foreign key

[Björn Platzen]

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_HS T"
(
"HST_ID" serial,
"HST_NR" varchar(10),
"HST_NAME" varchar(256),
"HST_COORD" public.geometry ,
CONSTRAINT "T_OEPNV_HST_pk ey" PRIMARY KEY ("HST_ID"),
CONSTRAINT "T_OEPNV_HST_un r" UNIQUE ("HST_NR"),
CONSTRAINT "enforce_geotyp e_HST_COORD" CHECK geometrytype("H ST_COORD")
= 'POINT'::text OR "HST_COORD" IS NULL,
CONSTRAINT "enforce_srid_H ST_COORD" CHECK srid("HST_COORD ") = 31467
) WITH OIDS;

2nd Table:

CREATE TABLE "T_OEPNV_HST_LI N" (
"HST_LIN_ID " serial primary key,
"HST_NR" varchar(10) references "T_OEPNV_HS T" on update cascade,
"LIN_U_NAME " varchar(255) references "T_OEPNV_UNTERN EHMEN" ("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_LI N" ("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_HS T" where "HST_NR"='10001 ';
returns
HST_ID | HST_NR | HST_NAME | HST_COORD
--------+--------+----------+------------------------------------------
23555 | 10001 | Afföller | SRID=31467;POIN T(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_LI N" because there is no HAST_NR='10001' in
"T_OEPNV_HS T".

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

--
momatec GmbH
www.momatec.de

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

[Michael Fuhr]

On Thu, Nov 11, 2004 at 08:13:16AM +0100, Bjoern Platzen wrote:

insert into "T_OEPNV_HST_LI N" ("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_HS T" where "HST_NR"='10001 ';
returns
HST_ID | HST_NR | HST_NAME | HST_COORD
--------+--------+----------+------------------------------------------
23555 | 10001 | Aff?ller | SRID=31467;POIN T(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

[Tom Lane]

=?iso-8859-1?Q?Bj=F6rn_Pla tzen?= <bj************ @momatec.de> writes:

CREATE TABLE "T_OEPNV_HS T"
(
"HST_ID" serial,
"HST_NR" varchar(10),
...
CONSTRAINT "T_OEPNV_HST_pk ey" PRIMARY KEY ("HST_ID"),
CONSTRAINT "T_OEPNV_HST_un r" UNIQUE ("HST_NR"),
...
CREATE TABLE "T_OEPNV_HST_LI N" (
"HST_NR" varchar(10) references "T_OEPNV_HS T" on update cascade,
); So, I can't understand, why I get the error, that I can't insert the
data into "T_OEPNV_HST_LI N" because there is no HST_NR='10001' in
"T_OEPNV_HS T".

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

When I try the example I get

ERROR: insert or update on table "T_OEPNV_HST_LI N" violates foreign key constraint "T_OEPNV_HST_LI N_HST_NR_fkey"
DETAIL: Key (HST_NR)=(10001 ) is not present in table "T_OEPNV_HS T".

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*******@postg resql.org so that your
message can get through to the mailing list cleanly

[gnari]

From: "Björn Platzen" <bj************ @momatec.de>

"HST_NR" varchar(10) references "T_OEPNV_HS T" 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