What are constraints?

In order to ensure the integrity (accuracy and correctness) of the data in the table, some restrictions are added to the table. It is a basic rule of table design in database. Using constraints can make data more accurate and reduce redundant data (dirty data).

Database integrity constraints are divided into the following categories: primary key constraints, unique constraints, non empty constraints, default constraints, and foreign key constraints.

1. Primary key constraint

Theoretically, each data table must have a unique primary key as the unique identification of the data. The column set as the primary key cannot be empty. The primary key habitual id indicates that it can be specified directly when creating data or added directly by modifying the table structure. The column set as the primary key cannot be repeated when adding data, that is, uniqueness.

Adding two pieces of data with the same primary key value will report an error

INSERT INTO student(id,name,age,email) VALUES (1,'Zhang an',18,'1443005893@qq.com')
Affected rows: 1
 time: 0.001s

INSERT INTO student(id,name,age,email) VALUES (1,'Li Si',20,'1443005893@qq.com')
1062 - Duplicate entry '1' for key 'PRIMARY'
time: 0s

The primary key is generally not an application column in the data table, but an additional column. It is troublesome to maintain it manually. Therefore, mysql has customized an automatic growth strategy for the primary key. It does not need to give the value manually, but let mysql maintain the primary key by itself. The method is to add 1.0 to the largest ID value in the data table every time

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,//Set auto increment
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

Auto increment Auto_ Characteristics of increment
        1. Only shaping data columns can be set to auto increment
        2. Only the primary key can be set as a self incrementing column
        3. Automatic growth, no assignment can be made when adding data
        4. The default value for initialization is 1, and the default increment is 1
        5. Auto increment column will not appear once it has been used

2. Unique constraints

The unique constraint is used to protect the data in a column in the table from duplication. It is similar to the primary key constraint, but the level is not as high as the primary key constraint. Multiple unique constraints can be created in a table, and the columns of unique constraints can be empty. Note that when the varchar length is 255, the unique constraint cannot be set. Unique constraints are generally used to restrict mobile phone number, account, email and other information.

Inserting the same value into the 'email' field will report an error.

INSERT INTO student(name,email) VALUES ('Zhang an','1443005893@qq.com')
Affected rows: 1
 time: 0.001s

INSERT INTO student(name,email) VALUES ('Zhang an','1443005893@qq.com')
1062 - Duplicate entry '1443005893@qq.com' for key 'email'
time: 0.004s

3. Non NULL constraint

You can use not null to set that a column in the data table is a required field and cannot be empty.

 4. Default constraint

You can set the default value constraint through default. If you do not give a value, the column with the default constraint will be filled with the default value.

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT 18 ,//Set defaults
  `email` varchar(20)  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

5. Foreign key constraints

Foreign key constraints are used to establish associations between the data of two tables. MySql database is a relational database. There is a relationship between tables, and this relationship becomes a foreign key relationship.