Re: Embedded SQL: foreign keys.

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(at)oper(dot)metu(dot)edu(dot)tr
devrim(dot)gunduz(at)linux(dot)org(dot)tr
devrimg(at)tr(dot)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
------------------------------------------------------------------