What is a primary key

A PRIMARY KEY constraint is a single column or a combination of columns whose value uniquely identifies a row in a table. Such a column or columns is called the primary key of the table by which entity integrity of the table can be enforced.

For example, if a person’s ID number uniquely identifies a person, that id number is the primary key. If you find two people by their ID numbers, but they have different names. The “ID number + name” can determine the only person, then the combination of “ID number + name” is the primary key.

Select the field that sets the primary key constraint

A single column primary key can be called a single-column primary key, or just a primary key. A primary key composed of multiple columns is called a compound primary key. For example, a case where id number + name determines a unique piece of data is a double primary key.

Primary keys should follow the following rules:

  • Only one primary key can be defined per table.

  • Following the uniqueness principle, the primary key value must uniquely identify each row of data in a table and cannot be NULL. That is, two rows of data in a table cannot have the same primary key value.

  • A column name can appear only once in the composite primary key list. For example, the id card + Name key cannot be the ID card + ID card + Name key.

  • Following the principle of minimization, the compound primary key cannot contain unnecessary redundant columns. When a column of the compound primary key is deleted, if the remaining columns still satisfy the principle of uniqueness, the compound primary key is incorrect. This is the minimization principle.

Three, important matters needing attention

If a table is not actively set as a primary key when it is being built, the first unique index column without NULL is selected as the primary key column and used as a clustered index (also known as a clustered index).

If there is no such index, a clustered index (also called a clustered index) is generated using the row number as the primary key. This row number is 6 bytes in length and is incremented. You can use the select _rowid from table statement.

Do not use automatically generated primary keys. This rule is not good for system development. Use this rule as a mandatory development constraint.

4, set the primary key method

  1. In the CREATE TABLE statement, the PRIMARY KEY is specified by the PRIMARY KEY keyword. The PRIMARY KEY is specified along with the column definition. The syntax is: column name column type PRIMARY KEY [default]

Example 1, create the DEMO_Employee 3 data table in the DEMO_DB database with the primary key ID. The test SQL statement and execution result are as follows:

mysql> CREATE TABLE demo_employee3
    -> (
    -> id INT(11) PRIMARY KEY- >name VARCHAR(25),
    -> deptid INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC demo_employee3;
+--------+-------------+------+-----+---------+-------+
| 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)
Copy the code
  1. [CONSTRAINT < CONSTRAINT name >] PRIMARY KEY [column name]

Example 2, create the DEMO_Employee 4 data table in the DEMO_DB database with the primary key ID. Test the SQL statement and the execution result as shown below.

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

The syntax of a PRIMARY KEY is as follows: PRIMARY KEY [field 1, field 2… field N]

Example 3: Create table demo_employee5. Assume that there is no primary key ID in the table. To uniquely identify an employee, combine name and deptid as the primary key.

mysql> CREATE TABLE demo_employee5
    -> (
    -> name VARCHAR(25),
    -> deptid INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptid)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC demo_employee5;
+--------+-------------+------+-----+---------+-------+
| 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)
Copy the code
  1. ALTER TABLE < TABLE name > ADD PRIMARY KEY(< column name >);

Example 4: Modify the demo_employee2 data table and set the field ID to the primary key. The following table shows the TEST SQL statement and execution result.

Start by looking at the table structure of the DEMO_employee2 data table, as shown below, which does not have any primary keys set.

mysql> DESC demo_employee2;
+--------+-------------+------+-----+---------+-------+
| 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)
Copy the code

The ALTER statement is then used to modify the table while adding the primary key

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

MySQL is one of the most correct databases used in daily work, so it is necessary to have a more in-depth and comprehensive grasp of distributed transactions, various database locks, propagation mechanisms and so on for high-level personnel.