Mysql drop primary key doesn't work

Question

Here is my table:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| template_id | int(11)      | NO   | MUL | NULL    |                |
| type        | smallint(6)  | NO   |     | 2       |                |
| width       | varchar(100) | NO   |     |         |                |
| height      | varchar(100) | NO   |     |         |                |
+-------------+--------------+------+-----+---------+----------------+

As you can tell from the table, the id and template_id are the primary key, and the id has an auto_increment setting.

What I want to do is drop the tempalte_id primary key attribute.

Here is the mysql query string I tried:

ALTER TABLE ts_template_size
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`);

The query could execute successfully, but seems nothing changed. No warning, no error, and the tempalte_id's primary key attribute still there

So how can I fix this? What's wrong with my query?

Possible duplicate of [SQL keys, MUL vs PRI vs UNI](http://stackoverflow.com/questions/5317889/sql-keys-mul-vs-pri-vs-uni) — Dezza, Oct 27 '16 at 13:47
What makes you think that `template_id` is a primary key? I can't tell it from the table structure you've shown. — Dezza, Oct 27 '16 at 13:48

ScaisEdge · Answer 1 · 2016-10-27T14:00:49.383

1

Alter for autoincrement

ALTER TABLE ts_template_size MODIFY id INT NULL;

Drop

ALTER TABLE ts_template_size
DROP PRIMARY KEY;

And recreate it:

ALTER TABLE yourtable
ADD PRIMARY KEY (`id`);

edited Oct 27 '16 at 14:00

answered Oct 27 '16 at 13:52

ScaisEdge

131,976
10
91
107

Directly drop primary key could cause an error: Incorrect table definition; there can be only one auto column and it must be defined as a key – hh54188 Oct 27 '16 at 14:00
Try altering for remove the autoincrement first – ScaisEdge Oct 27 '16 at 14:04

score 1 · Answer 2 · edited May 23 '17 at 12:16

1

"What's wrong with my query?"

Your query is dropping the primary key id in your table and then immediately re-adding it. There is no error message because the query works.

The problem is that template_id is not a primary key in your table. This should work:

ALTER TABLE ts_template_size drop index `template_id`;

Your id primary key will remain, as you desire.

See this answer for more on the MUL index designation (which is what template_id is in your table): https://stackoverflow.com/a/15268888/1250190

edited May 23 '17 at 12:16

Community

1
1

answered Oct 27 '16 at 14:09

pjd

1,163
8
17

@hh54188 The create table query you show in http://stackoverflow.com/q/40297345/1250190 verifies that `template_id` is not a primary key in the `ts_template_size` table. – pjd Oct 28 '16 at 15:02

Mysql drop primary key doesn't work

2 Answers2