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

Bug #48468	No multi-column UNIQUE constraint support
Submitted:	2 Nov 13:42	Modified:	19 Nov 20:12
Reporter:	Karsten Wutzke
Status:	Not a Bug
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.6a	OS:	Any
Assigned to:		Target Version:	WB52
Tags:	unique constraint, multiple columns
Triage:	Needs Triage: D1 (Critical)

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...