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.





Fri, 30 Nov 2001 10:23:02 -0600
"Mike Smialek" <_ike_mialek@hotmail.com>
case sensititvity bug in foreign keys on cygwin
"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

Mon, 03 Dec 2001 13:32:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: case sensititvity bug in foreign keys on cygwin 
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

Tue, 04 Dec 2001 09:34:37 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: case sensititvity bug in foreign keys on cygwin