ANSI SQL, (named) foreign keys in "create table" statement?

[dejan]

Hello everybody, recently I have tried to migrate some small tables from one system (MySQL) to Ingres 9.3 and I have found that Ingres does not allow ANSI SQL (92) field declarations for foreign keys.

First I thought (like I always do ) that it was my mistake, so I tried to make a toy database with just two tables. SQL code follows:

CREATE TABLE services (
       service_id INTEGER NOT NULL
     , name VARCHAR(20)
     , PRIMARY KEY (service_id)
);

CREATE TABLE customers (
       customer_id INTEGER NOT NULL
     , name VARCHAR(30) NOT NULL
     , service_id INTEGER NOT NULL
     , PRIMARY KEY (customer_id)
     , customers_services_fk FOREIGN KEY (service_id)
                  REFERENCES services (service_id) ON DELETE CASCADE ON UPDATE CASCADE
);

Looks like INGRES does not like the line where I wanted to specify foreign key constraint.

However, if I use ALTER TABLE code below everything is fine.

ALTER TABLE customers
  ADD CONSTRAINT customers_services_fk
      FOREIGN KEY (service_id)
      REFERENCES services (service_id)
   ON DELETE CASCADE
   ON UPDATE CASCADE;

Final note - I am aware that INGRES accomplishes what I initially wanted by using "CONSTRAINT <constraint_name> REFERENCES <table_name>(<field..>)" syntax. However, that syntax does not allow me to make my own name. Instead, INGRES makes some autogenerated one.

Maybe I missed something, but if INGRES does not allow ANSI SQL code from above I consider that a bug, something that should be fixed in next release. Still, I hope I am wrong, and that it all was my mistake.

If the initial SQL code should not work, please give me some rationale why.

After I modified my original SQL code and added ALTER TABLE code for adding foreign keys in fashion described above everything worked properly.


Kind regards

[rhann]

All I can say is it works as advertised for me, in the way ANSI/ISO describes. Perhaps a typo has got in the way?

[joe]

You're missing the CONSTRAINT keyword before customers_services_fk in the CREATE TABLE customers statement. [CONSTRAINT constraint_name] is optional, but CONSTRAINT is required if you're giving it a name.

[dejan]

Thanks joe, you were right.