What is the primary key in mysql?

In MySQL, the full name of the primary key is "primary key constraint". It is a column or a combination of multiple columns. Its value can uniquely identify each row in the table, and it can enforce the entity integrity of the table; primary key The function is to determine the uniqueness of the data, mainly used for foreign key association with other tables, as well as modification and deletion of this record.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

What is the primary key in mysql?

Primary key (PRIMARY KEY), also called "primary key constraint".

MySQL primary key constraint is a column or a combination of multiple columns whose value can uniquely identify each row in the table. Such a column or columns are called the table's primary key, by which the entity integrity of the table is enforced. The primary key is mainly used for foreign key associations in other tables, as well as modification and deletion of this record.

PRIMARY KEY 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.

The following points should be noted when using primary keys:

• Each table can only define one primary key.

• The primary key value must uniquely identify each row in the table and cannot be NULL, that is, there cannot be two rows of data with the same primary key value in the table. This is the principle of uniqueness.

• A field name can only appear once in the joint primary key field table.

• The joint primary key cannot contain unnecessary redundant fields. When a field in the joint primary key is deleted, if the primary key composed of the remaining fields still satisfies the uniqueness principle, then the joint primary key is incorrect. This is the principle of minimization.

Set the primary key constraint when creating the table

Set the primary key constraint when creating the data table, either for the table Set a primary key for a field in the table, or set a joint primary key for multiple fields in the table. But no matter which method is used, there can only be one primary key in a table. The following explains how to set a single-field primary key and a multi-field joint primary key.

1) Set a single-field primary key

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 1

Create the tb_emp3 data table in the test_db database, whose primary key is id, SQL statement and The running 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)

Or specify the primary key after defining all fields. The syntax format is as follows:

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

Example 2

Create the tb_emp4 data table in the test_db database, and its primary key is id , the SQL statements and running results are as follows.

mysql> CREATE TABLE tb_emp4
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp4;
+--------+-------------+------+-----+---------+-------+
| 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)

2) Set the joint primary key when creating the table

The so-called joint primary key means that the primary key is composed of multiple fields in a table.

For example, when setting up a student course selection data table, should the student number be used as the primary key or the course number as the primary key? If the student number is used as the primary key, then a student can only choose one course. If the course number is used as the primary key, then only one student can choose a course. Obviously, both of these situations are unrealistic.

In fact, when designing a student course selection schedule, the limitation is that a student can only choose the same course once. Therefore, the student number and course number can be put together as the primary key, which is a joint primary key.

The primary key is composed of multiple fields. The syntax format is as follows:

PRIMARY KEY [字段1，字段2，…,字段n]

Note: When the primary key is composed of multiple fields, the primary key constraint cannot be declared directly after the field name.

Example 3

Create the data table tb_emp5. Assume that there is no primary key id in the table. In order to uniquely identify an employee, you can combine name and deptId as the primary key. The SQL statement and running results are as follows.

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

Add primary key constraints when modifying the table

Primary key constraints can not only be created while creating the table, but can also be added when modifying the table . 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(<字段名>);

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)

Example 4

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)

Normally, when you want to set the primary key constraint of a field in the table when modifying the table, you must ensure that the values ​​in the field set as the primary key constraint cannot have duplicates and must be non-empty. . Otherwise, the primary key constraint cannot be set.

Delete primary key constraints

When a primary key constraint is not needed in a table, it needs to be deleted from the table. It is much easier to delete a primary key constraint than to create one.

The syntax format for deleting primary key constraints is as follows:

ALTER TABLE <数据表名> DROP PRIMARY KEY;

Example 5

Delete the primary key constraints in the tb_emp2 table. The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp2
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束。

【相关推荐：mysql视频教程】

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