Shulou(Shulou.com)06/01 Report--

This article is to share with you about the role of mysql in setting primary keys. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The function of mysql setting primary key is: 1, uniquely identify each row in the table, through it can force the physical integrity of the table; 2, mainly for foreign key association of other tables, as well as the modification and deletion of this record.

The purpose of setting the primary key by mysql is:

1. What is the primary key

A database primary key refers to a combination of columns or columns whose value uniquely identifies each row in the table, through which the physical integrity of the table can be enforced. The primary key is mainly used for the foreign key association of other tables, as well as the modification and deletion of this record.

2. The function of primary key

The primary key is the unique identification that can determine a record, the primary key field must be unique, must be non-empty, there can be only one primary key in a table, and the primary key can contain one or more fields.

For example, a record includes identity plus sign, name, age, school, nationality, gender, etc. The ID number is the only one that can identify you, and everything else may be duplicated, so the ID number is the primary key.

3. MySQL creates a table without a primary key

[root@node110] # mysql-uroot-pyinzhengjiemysql: [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 13Server version: 8.0.14 MySQL Community Server-GPLCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > CREATE DATABASE devops CHARACTER SET = utf8;Query OK, 1 row affected, 1 warning (0.00 sec) mysql > use devopsDatabase changedmysql > show tables;Empty set (0.00 sec) mysql > mysql > CREATE TABLE students (stu_id INT (11), stu_name VARCHAR (50), gender INT (11)) # first of all, we just created a very ordinary table here. Query OK, 0 rows affected (0. 01 sec) mysql > mysql > INSERT INTO students VALUES; # insert the first data Query OK, 1 row affected (0 sec) mysql > mysql > INSERT INTO students VALUES (2) # insert the second data Query OK, 1 row affected (0.01 sec) mysql > mysql > INSERT INTO students VALUES; # insert the third data, pay attention! This id is the same as the first insert data, don't ask me why I did it, I did it on purpose! Query OK, 1 row affected (0.00 sec) mysql > mysql > SELECT * FROM students # We query the three pieces of data we just inserted +-+ | stu_id | stu_name | gender | +- -- + | 1 | jason | 10 | | 2 | danny | 20 | | 1 | jenny | 30 | +-+ 3 rows in set (0.00 sec) mysql >

4. Create 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)); # take a closer look at this table creation statement. In addition to being different from the table name above, I also added primary key attributes to the stu_id field, as well as auto-growing attributes! Query OK, 0 rows affected (0. 01 sec) mysql > mysql > INSERT INTO student_primary VALUES. # here we insert the first data Query OK, 1 row affected (0. 00 sec) mysql > mysql > INSERT INTO student_primary VALUES. # here we insert the second piece of data Query OK, 1 row affected (0.01 sec) mysql > mysql > INSERT INTO student_primary VALUES. # here we made an error when we inserted the third piece of data! Prompt the primary key to repeat! ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'mysql > SELECT * FROM student_primary; # We look at the data in the table, and sure enough, there are only two pieces of data! The third piece of data is not inserted because it does not conform to our defined primary key rules! The primary key must be unique and not empty! +-+ | stu_id | stu_name | gender | +-+ | 1 | json | 10 | | 2 | danny | 20 | +-+ 2 rows in set (0.00 sec) mysql >

5. Create a table with a primary key (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; # insert the first data Query OK, 1 row affected (0.01 sec) mysql > mysql > INSERT INTO course VALUES (2) Query OK, 1 row affected (0. 00 sec) mysql > INSERT INTO course VALUES (3 Duplicates: 0mysql > mysql > SELECT * FROM course), (4 Duplicates: 0 Warnings: 0mysql > mysql > SELECT * FROM course +-thank you for reading! About what the role of mysql setting primary key is to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.