Database Design: optional, but must be unique if provided a value
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:
- How do I implement this in my database design (I'm using MySQL Workbench, by the way)
- Is there a potential problem with my model?
Just use a UNIQUE index on the column. See:
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.