[BUGS] ECPG: CREATE TABLE ... FOREIGN KEY(col) REFERENCES table(col)

[Lee Kindness]

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 or near "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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])