Can't insert date in field with foreign key - Mailing list pgsql-general

From Björn Platzen
Subject Can't insert date in field with foreign key
Date
Msg-id FB26DAD89E43D411B25F0050DA517F38311DCB@MOMATEC01
Whole thread Raw
Responses Re: Can't insert date in field with foreign key
Re: Can't insert date in field with foreign key
List pgsql-general
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



pgsql-general by date:

Previous
From: "Rolf Østvik"
Date:
Subject: Re: Important Info on comp.databases.postgresql.general
Next
From: "Ed L."
Date:
Subject: pg_ctl and stderr