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?

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.


Need Your Help

Div creating scroll bars

css html

http://jsfiddle.net/yDx55/

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.