MySQL Bugs: #48468: No multi-column UNIQUE constraint support

Bug #48468	No multi-column UNIQUE constraint support
Submitted:	2 Nov 2009 12:42	Modified:	19 Nov 2009 19:12
Reporter:	Karsten Wutzke	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.6a	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	multiple columns, unique constraint

Description:
With the current GUI (UQ check boxes next to NN in column list), it is not possible to specify multi-column unique constraints.

I flagged this as S2 serious, as it has a serious impact on the data: when not using natural primary keys, I want to declare the otherwise natural key columns as UNIQUE NOT NULL to avoid duplicates... (note, the NOT NULL implementation is OK in Workbench)

How to repeat:
See desc

Suggested fix:
Add a GUI that works much like specifying foreign keys. You can have several per table unlike PKs.

Corrected category

Thank you for the bug report. Could you please check if the same case of bug: http://bugs.mysql.com/bug.php?id=44450. Thanks in advance.

Hmm they seem to be similar, but not quite the same AFAI can see. In the other there's something involving auto_increment, which is not the point of this bug. It's rather, that there's no GUI to specify multi-column UNIQUE constraints.

Could you please try 5.2.6 release version. Thanks in advance.

I did. What do you want me discover. I see no difference. There's still no GUI to specify multi-column UNIQUE constraints, because the GUI only allows single columns to be flagged as UNIQUE via the UQ check boxes.

I think I totally understood you already.

It should be possible in any case to produce something like this in Workbench:
CREATE TABLE t(i integer, j integer, k integer, unique (i,j))

Verified as described.

And I agree here with you this is a serious problem because data lost could happen. In any case this occurs wrong create of indexes.

sorry for the confusion, but this new checkbox, which automatically creates a unique index for a column, is just a convenience-feature we've added to the column tab. 
If you want to manage more complex indexes you're supposed to do that, just like in previous versions, on the "Indexes"-Tab.

Well, I have another problem now:

Unique constraints are CONSTRAINTs, indexes are indexes, that is, indexes are *pure performance* structures. There is no SQL statement that will work or not work because an index is missing or not.

In contrast to a unique constraint, an index does not prevent duplicate rows, which is what keys/unique constraints are for! From a design perspective, indexes have nothing to do with constraints. Specifying primary key constraints and unique constraints has the consequence, that MySQL automatically generates an INDEX for these, but this is MySQL-specific behavior which has nothing to do with overall key/constraint design.

The point is: specifying unique constraints via the Indexes tab is plain wrong. Indexes are indexes, constraints are constraints.

In that effect, your the WB GUI is wrong:

1. Primary keys are created in the Columns tab, because each single PK flag is automatically added to the only primary key possible in a table.

2. Unique constraints can only be created via the Indexes tab, so the GUI is wrong, because it mixes constraint AND index specification.

Technically, this is wrong. So, either please either decouple their specification or rename the Indexes tab to reflect that you can specify unique constraints here!

I would like to see a Unique Constraints tab right where the Indexes tab is, the Indexes tab should be moved to the right of the Foreign Keys tab...

Ok, so what's new with this problem ? 
We all need multi-column UNIQUE constraint support. 
It's a bug, it's not a feature.

There _is_ multi-column unique constraint support. At this point this bug report is a complaint that it's accessible through the Indexes tab, while a constraint is not an index. (If the title can be editted, I'd suggest the developers do so, ... routinely.)

Personally, I wouldn't label it "Not a Bug", but rather "won't fix" (at least for now--the GUI could change in the future in a way that makes it seem more reasonable). It is a semantic inconsistency/error in the program, but it's one that has significant advantages. (You really want to create a separate Constraint tab that looks and behaves exactly like the Indexes tab, only with one available , "Unique"?)

There may be a more abstract label that could be used to keep them on the same tab while honoring the semantic distinction, but the probable obtuseness of it would likely create far more inconvenience for anyone just trying to create an index OR a uniqueness constraint than the current conceptual mincing does.

It's well and good for you to insist that multi-column constraints are supported, quite another to explain how to create them.  The manual too is of no help, and neither is an internet search.  So please spell it out: how does one create a uniqueness constraint on multiple columns?

what I understood from the previous comments, is that you go to the indexes tab of the table,add a new unique index and specify the columns you want to be unique in that case. You may say that this will make a unique index and not constraint, others will argue that that both are one and the same.

I did a little test on this issue.

I created the UNIQUE INDEX as Terry Nycum said at 8 Dec 2010 1:41.

The sql script exported is like this:

		SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
		SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
		SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

		CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
		USE `mydb` ;

		-- -----------------------------------------------------
		-- Table `mydb`.`test`
		-- -----------------------------------------------------
		CREATE  TABLE IF NOT EXISTS `mydb`.`test` (
		  `id` INT NOT NULL ,
		  `col1` INT NOT NULL ,
		  `col2` INT NOT NULL ,
		  PRIMARY KEY (`id`) ,
		  UNIQUE INDEX `index2` USING BTREE (`col1` ASC, `col2` ASC) )
		ENGINE = InnoDB;

		USE `mydb` ;

		SET SQL_MODE=@OLD_SQL_MODE;
		SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
		SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

		-- -----------------------------------------------------
		-- Data for table `mydb`.`test`
		-- -----------------------------------------------------
		START TRANSACTION;
		USE `mydb`;
		INSERT INTO `mydb`.`test` (`id`, `col1`, `col2`) VALUES (1, 2, 2);
		INSERT INTO `mydb`.`test` (`id`, `col1`, `col2`) VALUES (2, 2, 2);

		COMMIT;

When I run the script, I got an ERROR below.

		15:19:07	INSERT INTO `mydb`.`test` (`id`, `col1`, `col2`) VALUES (2, 2, 2)	Error Code: 1062. Duplicate entry '2-2' for key 'index2'	0.000 sec

So, the UNIQUE INDEX denied the insert action of duplicate data.