Обсуждение: 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 wrote:
>
> 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:

[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