MySQL Bugs: #39149: Missing name validation for index and constraint

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

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.

Thank you for a bug report. Verified just as described.

this functionality is provided via validationplugin - but only in SE version