What is the use of setting primary key in mysql?

Mysql sets the primary key to: 1. Uniquely identify each row in the table, through which the entity integrity of the table can be enforced; 2. Mainly used for foreign key associations in other tables, and this record Modification and deletion.

[Related learning recommendations: mysql tutorial]

The function of setting the primary key in mysql is:

1. What is a primary key?

A database primary key refers to a column or a combination of multiple columns. Its value can uniquely identify each row in the table, and it can enforce the consistency of the table. Entity integrity. The primary key is mainly used for foreign key associations in other tables, as well as modification and deletion of this record.

2. The role of the primary key

The primary key is the unique identifier that can determine a record. The primary key field must be unique and must be non-empty. There can only be one primary key in a table. The primary key can contain one or Multiple fields.

For example, a record includes identity number, name, age, school, nationality, gender, etc. The ID number is the only one that can identify you. Others may be duplicated. Therefore, the ID number is the primary key.

3. MySQL creates a table without a primary key

[root@node110 ~]# mysql -uroot -pyinzhengjie
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.14 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
mysql> 
mysql> CREATE DATABASE devops CHARACTER SET = utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> 
mysql> 
mysql> use devops
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> 
mysql> CREATE TABLE students(stu_id INT(11),stu_name VARCHAR(50),gender INT(11));　　　　　　#首先，我们这里只是创建了一张极为普通的表。
Query OK, 0 rows affected (0.01 sec)
mysql> 
mysql> INSERT INTO students VALUES(1,'jason',10);　　　　　　　　　　　　　　　　　　　　　　　　　　#插入第一条数据
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> INSERT INTO students VALUES(2,'danny',20);　　　　　　　　　　　　　　　　　　　　　　　　　　#插入第二条数据
Query OK, 1 row affected (0.01 sec)
mysql> 
mysql> INSERT INTO students VALUES(1,'jenny',30);　　　　　　　　　　　　　　　　　　　　　　　　　　 #插入第三条数据，注意！这个id和第一条插入的数据是相同的，别问我为什么这么干，我是故意这样搞的！
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM students;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#我们查询咱们刚刚插入的三条数据
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | jason    |     10 |
|      2 | danny    |     20 |
|      1 | jenny    |     30 |
+--------+----------+--------+
3 rows in set (0.00 sec)
mysql>

4. Creates a table with a primary key (student_primary)

mysql> CREATE TABLE student_primary(stu_id INT(11) PRIMARY KEY AUTO_INCREMENT,stu_name VARCHAR(50),gender INT(11));　　　　　　　　#仔细一下这个建表语句，除了和上面的表名不同，我还为stu_id字段加了主键属性，以及自动增长的属性！
Query OK, 0 rows affected (0.01 sec)
mysql> 
mysql> INSERT INTO student_primary VALUES(1,'json',10); 　　　　　　　　　　　　　　　　#这里我们插入第一条数据
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> INSERT INTO student_primary VALUES(2,'danny',20);　　　　　　　　　　　　　　　　#这里我们插入第二条数据
Query OK, 1 row affected (0.01 sec)
mysql> 
mysql> INSERT INTO student_primary VALUES(1,'jenny',30);      　　　　　　　　　　　　#这里我们插入第三条数据时，报错啦！提示主键重复！
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> 
mysql> 
mysql> SELECT * FROM student_primary;　　　　　　　　　　　　　　　　　　　　　　　　　　　　#我们查看表中的数据，果不其然，只有两条数据！第三条数据没有被插入进来，因为它不符合我们定义的主键规则！主键必须唯一且非空！
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | json     |     10 |
|      2 | danny    |     20 |
+--------+----------+--------+
2 rows in set (0.00 sec)
mysql>

5. Creates a table with a primary key Table(course)

mysql> CREATE TABLE course(id INT(11) PRIMARY KEY AUTO_INCREMENT,course_name VARCHAR(30));　　　　　　　　　　
Query OK, 0 rows affected (0.02 sec)
mysql> 
mysql> INSERT INTO course VALUES(1,'Chinese'); 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#插入第一条数据
Query OK, 1 row affected (0.01 sec)
mysql> 
mysql> INSERT INTO course VALUES(2,'English'); 
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO course VALUES(3,'Mathematics'),(4,'Physics'),(5,'Chemistry'),(6,'Biology');　　　　　　　　#咱们可以同时插入多条数据
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> 
mysql> SELECT * FROM course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Chinese     |
|  2 | English     |
|  3 | Mathematics |
|  4 | Physics     |
|  5 | Chemistry   |
|  6 | Biology     |
+----+-------------+
6 rows in set (0.00 sec)
mysql>

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