Bug #39149 Missing name validation for index and constraint
Submitted: 1 Sep 2008 7:25 Modified: 1 Sep 2008 20:47
Reporter: Lorenzo Luconi Trombacchi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.24 OS:Any
Assigned to: CPU Architecture:Any

[1 Sep 2008 7:25] Lorenzo Luconi Trombacchi
Description:
I don't know  if the right severity is S4 (feature request) or is a bug (S3).

You can set the same name to the indexes for a table and this create an invalid SQL create script:

CREATE  TABLE IF NOT EXISTS `tabl1` (
  `id` BIGINT UNSIGNED NOT NULL ,
  `a` VARCHAR(45) NULL ,
  `b` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX index1 (`a` ASC) ,
  INDEX index1 (`b` ASC) )
ENGINE = InnoDB;

> ERROR 1061 (42000): Duplicate key name 'index1'

Index name must be unique for a table, but constraint name must be unique for the entire database! Without a validation for constraint names, with a database with dozens tables, is easy to set the same name to different constraints. Probably the best is to not set a name for a constraint but you can't (see bug #39148).

 

How to repeat:
Create a table with two index with same name and try to export to sql or create more InnoDB tables with at least two foreign key with the same name.
[1 Sep 2008 12:08] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[1 Sep 2008 20:47] Johannes Taxacher
this functionality is provided via validationplugin - but only in SE version