• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

foreign keys in mySql

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys,

Im having trouble creating foreign keys in mysql. It wont let me add a foreign key from a serive table, or county table.

Im trying to add one field from each,service_name and county_name respectively to a table called countyservices.

But it keeps on throwing back errors to me saying that i cant do it.

Any advice?
[ April 26, 2005: Message edited by: Bear Bibeault ]
 
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Foreign key support is actually new to MySQL. Make sure you have the latest version and check with the documentation. It is picky in places, here is an excerpt from the docs.

Starting from MySQL 3.23.44, InnoDB features foreign key constraints.

The syntax of a foreign key constraint definition in InnoDB looks like this:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

Foreign keys definitions are subject to the following conditions:

*

Both tables must be InnoDB type.
*

In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
*

In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
*

Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.
 
Onion rings are vegetable donuts. Taste this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic