case sensititvity bug in foreign keys on cygwin
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.
"Mike Smialek" <_ike_mialek@hotmail.com> writes:
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
I see no bug here. You didn't quote the foreign key column name, thus
it got folded to lowercase.
It might be nice if the error message explicitly identified the key
columns being sought, though.
regards, tom lane
Mike Smialek wrote:
Configuration:
Windows 2000 Server
cygwin 2.78.2.9
PostgreSQL 7.1.3
psqlODBC 7.1.8
pgAdmin II 1.1.66Bug:
Capital letters cannot be used in column names used in foreign key
constraintsAll Smalls succeeds:
[snip]
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) );
You aren't double quoting the column name Significanceid
in the foreign key contraint clauses. Why ?
regards,
Hiroshi Inoue