Home  >  Article  >  Database  >  How to delete primary key in mysql?

How to delete primary key in mysql?

青灯夜游
青灯夜游Original
2019-05-08 09:39:1632313browse

After setting a primary key in a table, what should I do if I want to delete the primary key? The following article will introduce to you how to delete the primary key in MySQL. I hope it will be helpful to you.

How to delete primary key in mysql?

First let’s take a look at the syntax of deleting the primary key:

ALTER  TABLE  TABLE_NAME  DROP  PRIMARY  KEY;

There are two situations to consider when deleting the primary key in MySQL:

1. The primary key column does not have any constraints and the primary key can be deleted directly.

Example:

mysql> create table test1_3(
    -> id int not null primary key,
    -> name char(10)
    -> );
Query OK, 0 rows affected (0.01 sec)

We can directly use drop to delete the primary key

mysql> alter table test1_3 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2. If It is the primary key of auto-increment (AUTO_INCREMENT attribute)

Example:

mysql> create table test1_2(
    -> id int not null  auto_increment,
    -> name char(10),-> primary key(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc test1_2;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

If deleted directly, an error will be reported

mysql> alter table test1_2 drop primary key;

Output:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key               
#这说明此列是自动增长列,无法直接删除

The attributes of the column are still With AUTO_INCREMENT, you must first remove the automatic growth attribute of this column before you can delete the primary key.

mysql> alter table test1_2 modify id int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test1_2 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

The above is the detailed content of How to delete primary key in mysql?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn