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.