• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

foreign keys in mySql

 
Reuben Fields
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 ]
 
Jeremy Wilson
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic