Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

  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?
like image 491
StackOverflowNewbie Avatar asked Nov 30 '10 20:11

StackOverflowNewbie


3 Answers

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.

like image 73
MusiGenesis Avatar answered Oct 13 '22 20:10

MusiGenesis


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

like image 38
Hugues Van Landeghem Avatar answered Oct 13 '22 19:10

Hugues Van Landeghem


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

like image 28
skajfes Avatar answered Oct 13 '22 19:10

skajfes