Login Register Actian.com  

Actian Community Forum


Go Back   Actian Community Forums > Ingres Forums > Database General
 

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 2009-07-07   #1 (permalink)
Ingres Community
 
dejan's Avatar
 
Join Date: Jun 2009
Location: London, UK
Posts: 458
Default ANSI SQL, (named) foreign keys in "create table" statement?

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:

Code:
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.
Code:
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
dejan is offline   Reply With Quote
Old 2009-07-07   #2 (permalink)
Ingres Community
 
rhann's Avatar
 
Join Date: Mar 2007
Location: roy.hann@rationalcommerce.com
Posts: 1,425
Blog Entries: 32
Default

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?
__________________
Roy Hann
rhann is offline   Reply With Quote
Old 2009-07-07   #3 (permalink)
joe
Ingres Community
 
Join Date: Feb 2007
Posts: 185
Default

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.
joe is offline   Reply With Quote
Old 2009-07-09   #4 (permalink)
Ingres Community
 
dejan's Avatar
 
Join Date: Jun 2009
Location: London, UK
Posts: 458
Default

Thanks joe, you were right.
dejan is offline   Reply With Quote

Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


© 2011 Actian Corporation. All Rights Reserved