Creating UNIQUE constraint on multiple columns in MySQL Workbench EER diagram

Question

In MySQL Workbench's EER diagram, there is a checkbox to make each column in a table unique, not null, primary key etc.

However, I would like to have a UNIQUE constraint on multiple columns. Is it possible to add it in in MySQL Workbench's EER diagram?

EDIT: Ok, I realised the unique checkbox, creates a UNIQUE INDEX, and not a UNIQUE CONSTRAINT

Progestin

Answer 1

In the Alter Table dialog of MySQL Workbench:

• Go to Indexes tab.
• Double-click on a blank row to create a new index.
• Choose 'UNIQUE' as the index type.
• Check the columns that you want to be unique together.

There's some discussion as to whether this is weird, since an index is not the same as a constraint. I certainly wouldn't have thought to look there. However, apparently the `unique index' enforces uniqueness in the same way as a unique constraint, and may improve performance. For example, if I try to insert a row that would break unique together after using this method, it throws an '1062 Duplicate entry' error.

Bestiality

Should I delete the other indexes after create the new one ? – Extraterrestrial

Answer 2

it does not seem to be available : http://bugs.mysql.com/bug.php?id=48468 . it seems what you can is to create a multi column unique index on the indexes tab but for a multi column unique constraint, you need to run the creation command manually.

Underwaist

Thanks for the answer, they should really implement it. It's quite troublesome when you have multiple tables with multi column unique constraint. – Progestin

Actually, if you look through to the end of that thread it says that the multi-column unique index enforces uniqueness constraints properly. – Bestiality

yes it does but the question is about being able to create constraints and not indexes from work bench. it is possible from command line but not possible from workbench. – Underwaist

A rather verbose explanation about the difference – Ermey

Answer 3

With latest MWB (I'm on 6.0.8), it is possible to create composite keys

If you wish to create a composite primary key you can select multiple columns and check the PK check box. However, there is an additional step that is required, you must click the Indexes tab, then in the Index Columns panel you must set the desired order of the primary keys.

Patois