How to add primary key constraints when modifying a table in MySQL?

In mysql, you can use the "ALTER TABLE table name ADD PRIMARY KEY (field name);" statement to add primary key constraints when modifying the data table; when modifying the table, you need to set the primary key constraint of a field in the table. , make sure that the values ​​in the fields set as primary key constraints cannot have duplicates, and must be non-null.

(Recommended tutorial: mysql video tutorial)

The full name of the primary key (PRIMARY KEY) is "primary key constraint ” is the most frequently used constraint in MySQL. Under normal circumstances, in order to facilitate the DBMS to find records in the table faster, a primary key will be set in the table.

Set primary key constraints when creating the table

In the CREATE TABLE statement, specify the primary key through the PRIMARY KEY keyword.

Specify the primary key while defining the field. The syntax format is as follows:

<字段名> <数据类型> PRIMARY KEY [默认值]

Example

Create the tb_emp3 data table in the test_db database, whose primary key is id, SQL statement and run The results are as follows.

mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

Add primary key constraints when modifying the table

Primary key constraints can not only be created when the table is created, but can also be added when the table is modified. However, it should be noted that null values ​​are not allowed in fields set as primary key constraints.

The syntax format for adding primary key constraints when modifying the data table is as follows:

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

Normally, when you want to set the primary key constraint of a field in the table when modifying the table, be sure to set The values ​​in the fields that form primary key constraints cannot have duplicates and must be non-null. Otherwise, the primary key constraint cannot be set.

Example

View the table structure of the tb_emp2 data table. The SQL statement and running results are as follows.

mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

Modify the data table tb_emp2 and set the field id as the primary key. The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp2
    -> ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.12 sec)

The above is the detailed content of How to add primary key constraints when modifying a table in MySQL?. For more information, please follow other related articles on the PHP Chinese website!