Thread: Embedded SQL: foreign keys.

Hi,

We have a problem with the next table declarations:


EXEC SQL CREATE TABLE table1(

    p1 int4,
    p2 varchar(10),
    p3 varchar(20),
    CONSTRAINT table1_1 PRIMARY KEY (p1)
);
EXEC SQL COMMIT;

EXEC SQL CREATE TABLE table2(
    s1 int4,
    s2 int4,
    s3 varchar(10),
    s4 varchar(20),
    CONSTRAINT table2_1 PRIMARY KEY (s2, s1),
    CONSTRAINT table2_2 FOREIGN KEY (S2) REFERENCES table1 (p1) DEFERRABLE
);
EXEC SQL COMMIT;

EXEC SQL CREATE TABLE table3 (
    x1 int4,
    x2 int4,
    x3 int4,
    x4 varchar(16),
    CONSTRAINT table3_1 PRIMARY KEY (x3, x2, x1),
    CONSTRAINT table3_2 FOREIGN KEY (x3) REFERENCES table2 (s1) DEFERRABLE
);
EXEC SQL COMMIT;

 After compile, when we run the program, we obtain the next error in the
third "COMMIT";

Err code: -400
    ERROR: UNIQUE constraint matching given keys for referenced table
"table2" not found.

We are using postgres 7.1.3, could you help us?

Thanks.

Hi,

On Mon, 25 Feb 2002, TPCCUVA wrote:

>
> EXEC SQL CREATE TABLE table2(
>     s1 int4,
>     s2 int4,
>     s3 varchar(10),
>     s4 varchar(20),
>     CONSTRAINT table2_1 PRIMARY KEY (s2, s1),
  -->                    ^^^^^^^^


>     CONSTRAINT table2_2 FOREIGN KEY (S2) REFERENCES table1 (p1) DEFERRABLE
> );
> EXEC SQL COMMIT;
>
> EXEC SQL CREATE TABLE table3 (
>     x1 int4,
>     x2 int4,
>     x3 int4,
>     x4 varchar(16),
>     CONSTRAINT table3_1 PRIMARY KEY (x3, x2, x1),
>     CONSTRAINT table3_2 FOREIGN KEY (x3) REFERENCES table2 (s1) DEFERRABLE
> );
> EXEC SQL COMMIT;
>
>  After compile, when we run the program, we obtain the next error in the
> third "COMMIT";
>
> Err code: -400
>     ERROR: UNIQUE constraint matching given keys for referenced table
> "table2" not found.
>


In table 2, you define a primary key with two fields... However, in table
3, you call it with one argument...

The following works (of course, you could re-write it in other way due to
your purposes):

CREATE TABLE table1(
    p1 int4,
        p2 varchar(10),
        p3 varchar(20),
        CONSTRAINT table1_1 PRIMARY KEY (p1)
);

CREATE TABLE table2(
        s1 int4,
        s2 int4,
        s3 varchar(10),
        s4 varchar(20),
        CONSTRAINT table2_1 PRIMARY KEY (s1),
        CONSTRAINT table2_2 FOREIGN KEY (S2) REFERENCES table1 (p1) DEFERRABLE
            
);
            

CREATE TABLE table3 (
        x1 int4,
        x2 int4,
        x3 int4,
        x4 varchar(16),
        CONSTRAINT table3_1 PRIMARY KEY (x3, x2, x1),
        CONSTRAINT table3_2 FOREIGN KEY (x3) REFERENCES table2 (s1) DEFERRABLE
);


Regards,

Devrim GÜNDÜZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr
devrimg@tr.net

Phone : +90-535-328-9594 (cellular)
Phone : +90-312-295-9595 (work)
Phone : +90-312-286-5906 (home)

Web : http://devrim.oper.metu.edu.tr
------------------------------------------------------------------