Putting a Unique index on columns that are NOT NULL


CREATE TABLE `test_unique` (
`id` int(10) unsigned NOT NULL auto_increment,
`col` varchar(5) default NULL,
`col2` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col` (`col`)
);


SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | | blah |
| 2 | | blah |
| 3 | | blah |
| 4 | blah | NULL |
+----+------+------+

UPDATE test_unique SET col=\N WHERE col='';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | NULL | blah |
| 2 | NULL | blah |
| 3 | NULL | blah |
| 4 | blah | NULL |
+----+------+------+
4 rows in set (0.00 sec)

ALTER TABLE test_unique modify col varchar(5) DEFAULT NULL, ADD UNIQUE INDEX (col);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0


This is how it's done.