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

#1

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

Quote:

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

#2

From: "Björn Platzen" <bjoern.platzen (AT) momatec (DOT) de>

Quote:

"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

#3

=?iso-8859-1?Q?Bj=F6rn_Platzen?= <bjoern.platzen (AT) momatec (DOT) de> writes:

Quote:

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 majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.postgresql.general comp.databases.postgresql.general

Re: Can't insert date in field with foreign key - 11-11-2004 , 02:18 AM

Re: Can't insert date in field with foreign key - 11-11-2004 , 02:34 AM

Re: Can't insert date in field with foreign key - 11-11-2004 , 09:59 AM