Обсуждение: 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

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/

Hello Michael,

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

OK. Now I know what's wrong. I thought (for some reason) that if the
columns have the same name, I would not have to reference an explicit
column by it's name.

Thanks (as well to gnari who pointed me to the same issue),

Bjoern.

=?iso-8859-1?Q?Bj=F6rn_Platzen?= <bjoern.platzen@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

From: "Björn Platzen" <bjoern.platzen@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