CODEWITHSUNDEEP
mysql
hh54188
hh54188

Reputation: 15646

Mysql drop primary key doesn't work

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?

Upvotes: 0

Views: 1299

Answers (2)

pjd
pjd

Reputation: 1173

"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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133390

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`);

Upvotes: 1

Related Questions