Detailed explanation of the six common constraint types in MySQL

Preface

When inserting data into a data table, sometimes there are special requirements for the inserted data, such as student grades cannot be empty, student ID numbers cannot be repeated, etc. This is when constraints come in handy.

The constraint type ensures the integrity and uniqueness of the table data by restricting the data in the rows or columns of the table. In this chapter, we mainly introduce six common MySQL constraint types.

1. not null

Ensure that NULL values ​​cannot be stored. If NULL is inserted, the insertion fails.

We define two data tables, student table and student_copy table. The student table sets relevant constraints, and the student_copy table is used as its comparison table without setting any constraints to observe the difference between the two.

In the student table, id is defined as not null, so NULL = NO for the id row, which means that the data in this row cannot be NULL, otherwise the insertion will fail.

There are no constraints in the student_copy table, so NULL = YES for any field, which means that users can insert null values ​​at will without any errors.

Q: In addition to explicitly specifying that inserting NULL will result in an error under the not null constraint, will the implicit NULL result in an error?

There are two ways to specify NULL, explicit and implicit.

Explicit means setting the column to NULL when inserting, while implicit means not assigning a value to the column when inserting, and no default value is specified for this column. The system defaults to NULL, so all these situations will trigger insertion control errors.

2. unique

Ensure that each row of a column must have a unique value, that is, each row of data in the specified column cannot be repeated.

The following two figures show the operations on the student table and the student_copy table. The id column of the student table has a unique constraint set, so duplicate operations will be displayed when the same id = 1 is inserted.

There are no constraints in the student_copy table, which means that users can insert any value without getting an error.

Q: Will an error occur when inserting a NULL value under a unique constraint?

In fact, this problem has been solved just now. When id = NULL is inserted into the student table, the system will not report an error. Unique only ensures that the stored value is unique. It can be a null value, but the null value must also be unique. Inserting NULL will result in an error.

3. default

Specifies the default value when no value is assigned to the column. In other words, when data is inserted, no value is given to the specified column, then its value is the default value specified by default.

After the student table specifies the name field and sets the default constraint, the default column has a name. When inserting data, if the name column is not specified, the name column will be filled according to the default value we set = 'Unnamed'.

The student_copy table does not have a default constraint set, so when data is inserted, unspecified columns will be filled with the system default value, which is NULL.

4. primary key

The combination of not null + unique ensures that a column has a unique identifier and cannot store NULL values.

The first few constraint types can be set for multiple columns at the same time, but the primary key can only constrain one column in a table. The column using the primary key constraint is called the primary key .

If the inserted data is repeated, the next data will fail to be inserted, and the inserted data cannot be NULL.

A table can only have one column as the primary key, and cannot have multiple columns as primary keys at the same time. When designing a table, it is generally best to set a primary key. Common primary keys are in numeric form.

Auto_increment primary key

In actual development, there are often a series of strategies to ensure that primary keys are not repeated. The most common method is to set an auto-increment primary key . The system can automatically assign data, and users can also intervene manually at the same time. Because MySQL has a built-in auto-increment primary key function, it is very simple to use.

In the figure below, the id column becomes an auto-increment primary key, so users do not need to intervene manually when inserting data.

Q: Perform the following operations and guess what the query result of the table will be?

It is known that the Id field is set as an auto-increment primary key.

Zhang San's id = null, because the auto-increment primary key follows the order, its id = 1; Li Si's id = 1, the primary key cannot be repeated, Li Si's id is the same as Zhang San's id, so the insertion fails; continue to insert Li Si's data, this time set id = 4, there is no duplication with the original data, so the insertion is successful; Wang Wu's id = null, according to the auto-increment primary key, it continues to go down in the previous order, so its id = 5;

So let's look at the results:

5. foreign key

Foreign keys are used to relate to the primary keys of other tables, ensuring referential integrity that the data in one table matches the values ​​in another table.

foreign key (字段名) references 主表(列)

Let's take an example to understand this. Now we create two tables and want to associate them. How should we do it?

First, let's think about this correspondence. One student corresponds to one class, and one class corresponds to multiple students. In the student table, id is the primary key, so we can set the student class_id as the foreign key to associate it with the class table.

Create a class table class and set classesId as the primary key

create table class (
 id int primary key auto_increment,
 name varchar(20)
);

Create a student table student and set id as the primary key

create table student (
 id int primary key auto_increment,
 name varchar(20),
 class_id int,
 foreign key (class_id) references class(id)
);

MUL indicates a foreign key constraint;

Rules for using foreign keys

The class ID inserted in the student table must exist in the class table;

The class table is empty. At this time, insert the student table data and give class_id = 1, but the class number in the class table is empty, so the insertion fails. The foreign key constraint specified by the student table must be the primary key of the class table; after the foreign key constraint is established, the class id in the class table can no longer be modified or deleted at will;

We cannot modify the record with id = 1 in the class table to id = 20, because Zhang San in the student table depends on the result of id = 1 in the class table.

Disadvantages of foreign keys

If we have two tables related to products and orders as follows

At this point, we can use the product ID in the order table and the product ID in the product table to establish a foreign key constraint. However, when there is a record with a product ID of 1 in the order table, the record with id = 1 in the product table cannot be deleted. Then this data will be stored permanently, but the product may not exist permanently and may be removed from the shelf. This is the contradiction when foreign keys are used to associate certain tables.

If we want to have the verification function of foreign key constraints and also want to solve the current contradiction, we can perform logical deletion : add a separate column to the product to mark whether the data is valid, set flag = 1 to indicate that the record is valid, and flag = 0 to indicate that the record is invalid. For the product to be deleted, you can directly change its flag to 0. Logically, the product has been deleted, but in fact the data is still stored in the table, which is not a real physical deletion.

6. check

Ensures that the values ​​in a column meet the specified condition.

Summarize

This concludes this article about the six common constraint types in MySQL. For more information about common MySQL constraint types, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!