DelphiFAQ Home Search:
General :: Databases :: mysql
General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

Create UNIQUE Index yields a not unique index in mysql - why?

Question:

I created an index on a mysql table named 'referrers' using CREATE UNIQUE INDEX and I get no error message but when I view the table definition afterwards with DESC referrers then the key on is described as MUL (multiple, not unique).
What is going on?

Answer:

You probably forgot to specify the field in question as NOT NULL.

See the example below. First attempt to add a unique index on field ref_url yields a 'MUL' key field.

Then the field gets dropped, redefined as NOT NULL and then the index definition is successful.

mysql> create unique index ref_idx1 on referrers (ref_url);
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> desc referrers;
 +---------+--------------+------+-----+---------+----------------+
 | Field   | Type         | Null | Key | Default | Extra          |
 +---------+--------------+------+-----+---------+----------------+
 | ref_id  | int(11)      |      | PRI | NULL    | auto_increment |
 | ref_url | varchar(250) | YES  | MUL |         |                |
 +---------+--------------+------+-----+---------+----------------+
 2 rows in set (0.00 sec)
 
 mysql> drop index ref_idx1 on referrers;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> alter table referrers drop ref_url;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> alter table referrers add ref_url varchar(250) not null default '';
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> create unique index ref_idx1 on referrers (ref_url);
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> desc referrers;
 +---------+--------------+------+-----+---------+----------------+
 | Field   | Type         | Null | Key | Default | Extra          |
 +---------+--------------+------+-----+---------+----------------+
 | ref_id  | int(11)      |      | PRI | NULL    | auto_increment |
 | ref_url | varchar(250) |      | UNI |         |                |
 +---------+--------------+------+-----+---------+----------------+
 2 rows in set (0.00 sec)
 
 mysql>
 

Generated 12:00:54 on Oct 21, 2017