File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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 Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "foreign keys in mySql" Watch "foreign keys in mySql" New topic
Author

foreign keys in mySql

Reuben Fields
Greenhorn

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, ...)
[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.


Jeremy Wilson
 
Consider Paul's rocket mass heater.
 
subject: foreign keys in mySql
 
Similar Threads
The First Steps.. you start here
JForum Woes...
About MySql
What effects by using a lot of foreign keys?
CMP Beans automatic generation of DB fields