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@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
------------------------------------------------------------------