MySQL Bugs: #17464: MTK does not port all FOREIGN KEY constraints correctly

Bug #17464	MTK does not port all FOREIGN KEY constraints correctly
Submitted:	16 Feb 2006 15:08	Modified:	17 Mar 2006 13:12
Reporter:	Kristian Koehntopp	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.23	OS:	Microsoft Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
In InnoDB the rules for FOREIGN KEY constraints differ from Oracle. MTK does not know about this and generated code that is not executeable SQL in MySQL. The relevant documentation is at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html.

How to repeat:
Example case:
create table master (
	m_id bigint unsigned not null,
	m_data integer not null
);

create table detail (
	d_id bigint unsigned not null,
	d_data integer not null,
	m_id bigint unsigned not null,
	constraint m_id_ref foreign key (m_id) references master(m_id)
);
This works in Oracle, but not in MySQL: ERROR 1005 (HY000): Can't create table '.\kris\detail.frm' (errno: 150). To make it work, master.m_id must be an index or unique index, and the foreign key constraint will automatically create an index on detail.m_id if necessary.
Additional limits apply: For example, in MySQL, "Index prefixes on foreign  key columns are not supported". 

Suggested fix:
If possible, add the necessary changes to the indexing, and make note about this. If not possible, make note about this, but in all cases, detect this.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".