Converting primary key into unique key in mysql

Question

I've a table like this :

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num   | int(11) | NO   | PRI | NULL    |       |
| t     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

If I need to change the constraint of num from primary key to a unique key, What shall I do? I did:

alter table c2 modify num integer unique key;
output:
mysql> desc c2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num   | int(11) | NO   | PRI | NULL    |       |
| t     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

Another one :

alter table c2 drop constraint num;
output : ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint num' at line 1

What else shall I use to change the primary key into a unique one???

why do you care if it is a primary key or a unique key? – ysth Sep 16 '20 at 04:57 — ysth, Sep 16 '20 at 04:57

ysth · Accepted Answer · 2023-05-03T17:44:14.280

Do show create table c2, not desc, to get a better idea of what indexes there are. Your first, successful, alter table added a unique key, but that doesn't remove the primary key. To also drop the primary key, do:

alter table c2 drop primary key;

Then show create table will show you that num is a unique key, not a primary key.

However, mysql (when using innodb) does require all tables to have a primary key; if you do not designate one, and there is a unique key all of whose columns are not null, it will use that as the primary key, or failing that, it will create a hidden column to use as the primary key. And apparently desc will still show a qualifying unique key as PRI, even though it is only being used as a primary key, not actually designated as one.

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=f1d7542f80421eca1b37a2373715f800

score 0 · Answer 2 · answered Sep 16 '20 at 04:50

first unique key will show as primary in desc command. But you can check with below command

show create table c2;

then you'll find UNIQUE KEY num (num) and if you can to remove unique key, you can drop with below alter query/command

alter table c2 drop key num;

Converting primary key into unique key in mysql

2 Answers2