Thread: 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