[BUGS] case sensititvity bug in foreign keys on cygwin

[Mike Smialek]

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.





---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html