MySQL Bugs: #1528: Foreign key names

Bug #1528	Foreign key names \| SQL dump
Submitted:	10 Oct 2003 17:36	Modified:	3 Aug 2007 16:05
Reporter:	Hadrian Oliver	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.0.14	OS:	Windows (windows/linux)
Assigned to:		CPU Architecture:	Any

Description:
All of the following refer to InnoDB type tables.

Please could you change the handling of foreign keys so that the names of the keys are remembered, and so they are reported by the 'show create table' command.

Also, as has been requested by others, please could you improve support for the sql dump so that it prioritises the order of table creation according to dependencies imposed by foreign keys.

Please could you have the sql-dump add semi-colons (;) after the ") Type = InnoDB". This would make it more user friendly for copy & pasting at the command line (in Windows) if nothing else.

And finally, please could you also change things so that table names are CASE SENSITIVE in both Windows & other distros, and that the "SHOW CREATE TABLE" commands report the correct case. I noticed that the Windows distro (4.0.14 / Win2K) reports table names in ALL LOWER CASE, despite the sql requesting mixed case names. The linux version does not make this error (Why am I still using Windows? Good question.).

Sorry for being picky - there are some trivial sounding complaints - but I'm writing a program to write generic 'web-frontends' for me in PHP and these sort of things really are a thorn in my side right now. 

Keep up the good work.

Yours faithfully,

A long standing fan of MySQL which, by my own tests, whips Oracle by almost as much as 10,000% (no exageration!) (based on a 'like' search retrieving 10,000 records (from a Java based application))!!!

How to repeat:
Create InnoDB type tables with MIXED CASE table names and NAMED FOREIGN KAYS

Run 'show create table' command on the tables.

NOTE:

1) SQL output does not end with semi-colons as required in Windows(2K) command-line MySql sql entry.

2) The sql for the tables is not created (except perhaps by chance) in the correct sequence. This means that tables with foreign keys to other tables could be included before the referenced tables, causing the script to fail when executed.

3) The names of the foreign keys are not remembered/reported in the output sql.

4) Creation on the same structure on Windows & Linux OS give different results when running the "SHOW CREATE TABLE" command.

Suggested fix:
Append semi-colons to the end of ALL sql commands when out-putting sql.

Add a logical sequencing routine prior to the sql dump script following obtaining the tables names but before itteration through the list of tables. This is likely to add significant overhead to the script which is bad; but it would make the script useable for all InnoDB users! And this isn't the type of command run repeatedly by many users.

Add proper support for foreign key names (don't forget them! my system needs them!!!).

Make all table name handling case-sensitive (or case-insensitive) in both Windows and other OS distros. I really don't mind which, but consistency is paramount.

I am assigning Heikki for complete comments. Two comments from me:

Your note 2:

From the Manual:
----------------------------------------------------------------------
If you want to import several dumps of tables, but the dumps are not
correctly ordered for foreign keys, starting from 3.23.52 and 4.0.3 you
can turn the foreign key checks off for a while in the import session:

SET FOREIGN_KEY_CHECKS=0;

This allows you to import the tables in any order, and also speeds up the
import.
-----------------------------------------------------------------------------

About CASE SENSITIVE

Currently this is based on OS support, Windows from NT and above offers case
SENSITIVE only for write but for read is CASE INSENSITIVE so demands a major
change on server code which is a big job. However let us Heikki, reply you.

adding to the above:

mysqldump in 4.1+ already adds SET FOREIGN_KEY_CHECKS=0; to the dump automatically, so this is solved.

We also do have plans to make table name case-sensitivity not to be dependent on underlying OS filesystem capabilities. But as Miguel noted, it's a big task which implies significant changes in the server code, and as such cannot not be done before 5.0.

Hi!

InnoDB now remembers the CONSTRAINT names that you gave.

There were also other items in the feature request. Therefore I am no closing this item.

Regards,

Heikki

According to comments from Heikki, this is fixed.