case sensititvity bug in foreign keys on cygwin

From: "Mike Smialek" <_ike_mialek(at)hotmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org(dot)pgsql-admin(at)postgresql(dot)org(dot)pgsql-bugs(at)postgresql(dot)org(dot)pgsql-cygwin(at)postgresql(dot)org
Subject: case sensititvity bug in foreign keys on cygwin
Date: 2001-11-30 16:23:02
Message-ID: 9u8bn5$j34$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-bugs pgsql-hackers

Configuration:
Windows 2000 Server
cygwin 2.78.2.9
PostgreSQL 7.1.3
psqlODBC 7.1.8
pgAdmin II 1.1.66

Bug:
Capital letters cannot be used in column names used in foreign key
constraints

All Smalls succeeds:

-- Table: significance
CREATE TABLE "significance" (
"significanceid" int4 NOT NULL,
"desc" varchar(255),
CONSTRAINT "pk_significance" PRIMARY KEY ("significanceid"));

-- Table: primaryword
CREATE TABLE "primaryword" (
"exerciseid" int4 NOT NULL,
"significanceid" int4 NOT NULL,
CONSTRAINT "pk_primaryword" PRIMARY KEY ("exerciseid"),
CONSTRAINT "fk_primaryword_significance" FOREIGN KEY
(significanceid) REFERENCES "significance" (significanceid) );

With just the foreign table name capitalized, it also succeeds:
-- Table: Significance
CREATE TABLE "Significance" (
"significanceid" int4 NOT NULL,
"desc" varchar(255),
CONSTRAINT "pk_significance" PRIMARY KEY ("significanceid"));

-- Table: primaryword
CREATE TABLE "primaryword" (
"exerciseid" int4 NOT NULL,
"significanceid" int4 NOT NULL,
CONSTRAINT "pk_primaryword" PRIMARY KEY ("exerciseid"),
CONSTRAINT "fk_primaryword_significance" FOREIGN KEY
(significanceid) REFERENCES "Significance" (significanceid) );

Capitalizing just the foreign column name fails with what seems to be an
incorrect error:
-- Table: significance
CREATE TABLE "significance" (
"Significanceid" int4 NOT NULL,
"desc" varchar(255),
CONSTRAINT "pk_significance" PRIMARY KEY ("Significanceid"));

-- Table: primaryword
CREATE TABLE "primaryword" (
"exerciseid" int4 NOT NULL,
"significanceid" int4 NOT NULL,
CONSTRAINT "pk_primaryword" PRIMARY KEY ("exerciseid"),
CONSTRAINT "fk_primaryword_significance" FOREIGN KEY
(significanceid) REFERENCES "significance" (Significanceid) );

Fails with error
Description: Error while executing the query;
Error: UNIQUE constraint matching given keys for refernced table
"significance" not found

Capitalizing just the child column name fails :
-- Table: Significance
CREATE TABLE "significance" (
"significanceid" int4 NOT NULL,
"desc" varchar(255),
CONSTRAINT "pk_significance" PRIMARY KEY ("significanceid"));

-- Table: primaryword
CREATE TABLE "primaryword" (
"exerciseid" int4 NOT NULL,
"Significanceid" int4 NOT NULL,
CONSTRAINT "pk_primaryword" PRIMARY KEY ("exerciseid"),
CONSTRAINT "fk_primaryword_significance" FOREIGN KEY
(Significanceid) REFERENCES "significance" (significanceid) );

With the following error:
Description: Error while executing the query;
Error: Columns referenced in foreign key constraint not found

I could not get foreign keys to succeed if there were any caps in the column
names, although caps in primary key constraints seems to work just fine.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Mikhail Terekhov 2001-11-30 16:24:24 Re: Visio
Previous Message Tom Lane 2001-11-30 15:21:51 Re: locale question

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2001-11-30 16:40:06 Re: Bug #526: Three levels deeply nested SELECT goes wrong
Previous Message Andreas Kretzer 2001-11-30 15:26:23 Handling large Objects with Tcl/Tk

Browse pgsql-hackers by date

  From Date Subject
Next Message Zeugswetter Andreas SB SD 2001-11-30 16:44:08 Re: History question
Previous Message Michael Meskes 2001-11-30 16:15:08 Re: History question