Create UNIQUE Index yields a not unique index in mysql

Databases
InterBase (28)
MS-SQL (5)
mysql (36)
Oracle (1)

Exchange Links

About this site

Links to us

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

1 comments. Current rating: 5 stars

(1 votes). Leave comments and/ or rate it.

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>

Comments:

2008-10-06, 13:14:49

anonymous

Very helpful. Thanks.

Email address (not necessary): Rate as	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option.

It seems that you are from Los Angeles, US . Info/ Feedback on this	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
Add a picture: Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material.


	Search: