How to add primary key in mysql?

The full name of "PRIMARY KEY" is "primary key constraint". A MySQL primary key constraint is a column or combination of columns whose value uniquely identifies each row in a table. Such a column or columns are called the table's primary key, by which the entity integrity of the table is enforced. So how to add primary key constraints in MySQL? The following article will introduce it to you.

#The primary key constraint defines a primary key in the table to uniquely determine the identifier of each row of data in the table. The primary key can be a certain column in the table or a combination of multiple columns. A primary key composed of multiple columns is called a composite primary key. Primary keys should comply with the following rules:

● 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 in the table with the same primary key value. This is the principle of uniqueness.

● A column name can only appear once in the composite primary key list.

● Composite primary key cannot contain unnecessary redundant columns. When a column of the composite primary key is deleted, if the primary key composed of the remaining columns still satisfies the uniqueness principle, then the composite primary key is incorrect. This is the principle of minimization.

1. Add primary key constraints when creating the table

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

Specify the primary key while defining the column. The syntax rules are as follows:

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

Example: Create the tb_emp 1 data table in the test_db database, and its primary key is id

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

In the definition After all columns are completed, the syntax format for specifying the primary key is:

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

Example: Create the tb_emp 2 data table in the test_db database, and its primary key is id

mysql> CREATE TABLE tb_emp2
    -> (
    -> 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_emp2;
+--------+-------------+------+-----+---------+-------+
| 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, Add primary key constraints after creating the table

After creating the table, you can add primary key constraints when modifying the data table. The syntax rules are:

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

Example: Modify the data table tb_emp3 and change the fields Set id as the primary key

mysql> ALTER TABLE tb_emp3
    -> ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_emp3;
+--------+-------------+------+-----+---------+-------+
| 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)

3. Set the composite primary key

You can also add a composite primary key when creating the table. At this time, the primary key is composed of multiple fields combined. Grammar rules As follows:

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

Example: Create the data table tb_emp4. 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

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

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