ECPG: CREATE TABLE ... FOREIGN KEY(col) REFERENCES table(col) - Mailing list pgsql-bugs

Hi,

When creating a table in embedded SQL, a foreign key construct like:
FOREIGN KEY(col) REFERENCES table(col)

is not accepted by ecpg. Consider the following code fragment (error
checking omitted):
 EXEC SQL CREATE TABLE   colscales(id   INTEGER     NOT NULL,      name VARCHAR(64) NOT NULL,      auto INTEGER     NOT
NULL,     PRIMARY KEY(id),      UNIQUE(name));
 
 EXEC SQL CREATE TABLE colscaledata(scale_id  INTEGER     NOT NULL,                 order_id  INTEGER     NOT NULL,
           threshold FLOAT       NOT NULL,                 colour    VARCHAR(10) NOT NULL,                 FOREIGN
KEY(scale_id)REFERENCES colscales(id));
 

This results in the following output from ecpg:
 src/colourscales_table.pc:55: ERROR: parse error, unexpected `NO', expecting `NOT' or `DEFERRABLE' or `INITIALLY' at
ornear "NO"
 

This is with ecpg 2.9.0, PostgreSQL 7.2 on Linux. The same(ish)
statments create the tables without problem with psql.

Obviously the workaround is to use something like:
FOREIGN KEY(col) REFERENCES table

but this would not work if the desired column was not the primary
key...

BRegards, Lee Kindness.