Creating UNIQUE constraint on multiple columns in MySQL Workbench EER diagram

zhenming picture zhenming · Jul 7, 2012 · Viewed 34.2k times · Source

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

Answer

Alex picture Alex · Apr 18, 2013

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.