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

This article mainly shows you "what are the common types of constraints in MySQL", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn what are the common types of constraints in MySQL "this article.

1. Not null

Make sure that the specified null value cannot be stored, and the insert fails if the NULL is inserted.

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

Id in the student table defines not null, so the NULL = NO for the id row means that this row of data cannot be NULL, or the insert will fail.

There are no constraints in the student_copy table, so the NULL = YES of any field means that the user is free to insert null values without reporting an error.

Under the Q:not null constraint, in addition to explicitly specifying that inserting NULL will cause an error, will the implicit NULL report an error?

NULL can be specified in two ways, explicit and implicit.

Explicitly refers to setting the column NULL on insert, while implicitly means that no value is assigned to the column at insert time, and no default value is specified for this column, and the default value of the system is NULL, in all cases, an error of insertion control will be triggered.

2. Unique

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

The following two figures show operations on the student table and the student_ copy table, respectively. The id column of the student table has a unique constraint set, so repeated operations are displayed when the same id = 1 is inserted.

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

Will an error be reported when inserting null values under Q:unique constraints?

In fact, this problem has been operated just now. If 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 null, but the null value must also be unique, and then insert NULL will report an error.

3. Default

Specifies the default value when there is no value assigned to the column, in other words, if the data is inserted without a value for the specified column, then the value is the default value specified by defalut.

After the student table specifies the name field and sets the default constraint, the column default has a name. When inserting data, the name column is not specified, so the name column will be populated according to the default value of 'unnamed' set by us.

The student_copy table does not have default constraints set, so when inserting data, the columns that are not specified are populated according to the system default value, which is NULL.

4. Primary key

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

The first several constraint types can be set for multiple columns at the same time, but only one column in a primary key table can be constrained, and the column that uses the primary key constraint is called the primary key.

And unique type, if the inserted data is repeated, then the latter piece of data will fail, and the inserted data cannot be NULL.

There can be only one column primary key in a table, and multiple columns cannot be primary keys at the same time. When designing a table, it is generally best to set a primary key, which is common in digital form.

Self-increasing primary key auto_increment

In the actual development, there are often a series of strategies to ensure that the primary key is not repeated, the most common method is to set the self-increasing primary key, the system can automatically assign data, and users can also intervene manually at the same time, because the MySQL has built-in self-increasing primary key function, so it is very easy to use.

In the following figure, the id column becomes a self-increasing primary key, so users don't have to intervene manually when inserting data.

Q: do the following to guess what the query result of the table is?

It is known that the Id field is set to the self-increasing primary key.

Zhang San's id = null, because the self-increasing primary key in order its id = 1; Li Si's id = 1, Li Si's id and Zhang San's id can not be repeated, so the insertion failed; continue to insert Li Si data, set id = 4 at this time, not repeat with the original data, so insert successfully; Wang Wu's id = null, go down according to the previous order of self-increasing primary key, so its id = 5

So let's take a look at the results:

5. Foreign key

Foreign keys are used to associate the primary keys of other tables to ensure the referential integrity of data in one table matching values in another table.

Foreign key (field name) references main table (column)

Let's take an example to understand. What should we do if we now create two tables and want to associate them?

First of all, think about this correspondence, one student corresponds to one class, and one class corresponds to multiple students. If id is the primary key in the student table, then we can set the student class_id as the foreign key to associate the class table.

Create the class table class and set classsId as the primary key

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

Create the 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 represents a foreign key constraint

Rules for the use of 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, while the class number is empty in the class table, so the insertion failed. The foreign key constraint specified in 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 change the record of id = 1 in the class table to id = 20, because Zhang San in the student table depends on the result of the class table id = 1.

Disadvantages of foreign keys

Join if we have the following two tables associated with an item-order

At this time, we can use the merchandise id in the order table and the merchandise id in the merchandise table to establish foreign key constraints, but when there is a record of commodity Id = 1 in the order table, it is impossible to delete the record of id = 1 in the merchandise table, then this data will last forever, but the goods may not exist forever, and they will probably be removed from the shelves, which is the contradiction when foreign keys are associated with some tables.

If we not only want to have the verification function of foreign key constraints, but also hope to resolve the current contradiction, then we can logically delete: add a separate column to the item to identify whether the data is valid, set flag = 1 to indicate that the record is valid, and flag = 0 means the record is invalid. The identity of the item that needs to be deleted can be changed to 0 directly. Logically, the logo has deleted the item, but in fact, the data is still stored in the table, which is not a physical deletion in the real sense.

6. Check

Ensures that the values in the column meet the specified conditions.

This is all the content of the article "what are the common types of constraints in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.