Re: Can't insert date in field with foreign key

=?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