mysql unique index EXCEPT empty or null

Question

I have a MySQL MyISAM table with ISBNs. I want to create a UNIQUE index that won't throw "duplicate" error if the value is empty or null.

Is this possible?

Answer 1

Since you're using MyISAM (or INNODB) storage engine, the short answer is no. Multiple empty strings won't work for the unique index constraint, even though multiple nulls would. Your best bet if you want a SQL only solution, is to create a regular index on the ISBN field, and then use a stored procedure to act as an insertion proxy--one that checks for uniqueness if not null or empty.

If just nulls work for you, here is a basic create:

CREATE TABLE `books` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `isbn` int(13) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `isbn` (`isbn`)
) 

Just a note... Remember the '13' specified for int is only the number of digits it will show when returning in a query, not the size of the integer.

Answer 2

you can use something like this,

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times). Refer MySQL INDEXES.