How to set the primary key when creating a table in MySQL?

Setting method: In the "CREATE TABLE" statement, specify the primary key through the "PRIMARY KEY" keyword, the syntax format is "field name data type PRIMARY KEY [default value]" or "[CONSTRAINT constraint name] PRIMARY KEY field name".

The full name of the primary key (PRIMARY KEY) is "primary key constraint", which 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.

(Recommended tutorial: mysql video tutorial)

Set primary key constraints when creating a table

When creating a data table To set primary key constraints, you can set a primary key for one field in the table or 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(id,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)

Related recommendations: php training

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