Database Design: optional, but must be unique if provided a value

Question

I have a column in one of my tables. It's optional, so it can be left blank. However, if a value is provided for that column, it must be unique. Two questions:

1. How do I implement this in my database design (I'm using MySQL Workbench, by the way)
2. Is there a potential problem with my model?

Answer 1

Just use a UNIQUE index on the column. See:

http://dev.mysql.com/doc/refman/5.1/en/create-index.html

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Answer 2

It's can be null (and not blank) and unique. By default value can be null. There is no problem for me.

Answer 3

You can create a UNIQUE index on a table. In MySQL workbench that is the UQ checkbox when creating/editing the table.