File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes foreign keys in mySql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "foreign keys in mySql" Watch "foreign keys in mySql" New topic

foreign keys in mySql

Reuben Fields

Joined: Apr 05, 2005
Posts: 15
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

Joined: Feb 18, 2003
Posts: 166
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, ...)

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.

Jeremy Wilson
I agree. Here's the link:
subject: foreign keys in mySql
It's not a secret anymore!