What are the common constraints in mysql?

Mysql common constraints include: 1. Non-null constraint "not null"; 2. Unique constraint "unique"; 3. Primary key constraint "primary key"; 4. Foreign key constraint "foreign key"; 5 , default value constraint "Default" and so on.

Recommendation: "mysql tutorial"

Constraints are a kind of restriction, which pass through the table Limit the row or column data to ensure the integrity and uniqueness of the table data. The following article will introduce you to 6 common MySQL constraints. I hope it will be helpful to you.

1. Non-null constraint (not null)

The non-null constraint is used to ensure that the value of the current column is not null. The non-null constraint can only Appears on a column of a table object.

Null type characteristics: All types of values ​​can be null, including int, float and other data types

2. Unique constraints (unique)

The unique constraint is that the columns or column combinations of the specified table cannot be repeated to ensure the uniqueness of the data.

Unique constraints do not allow duplicate values, but can be multiple nulls.

The same table can have multiple unique constraints and constraints on multiple column combinations.

When creating a unique constraint, if you do not give a unique constraint name, it will default to the same as the column name.

Unique constraints can not only be created within one table, but also combined unique constraints can be created in multiple tables at the same time.

3. Primary key constraint (primary key) PK

The primary key constraint is equivalent to the combination of the unique constraint non-null constraint. The primary key constraint column is not allowed to be repeated or appear. NULL value.

Each table allows only one primary key at most. Primary key constraints can be created at the column level or at the table level.

When creating a primary key constraint, the system will by default create a corresponding unique index on the column and column combination.

4. Foreign key constraint (foreign key) FK

Foreign key constraint is used to strengthen a column of two tables (master table and slave table) Or the connection between multiple columns of data, which can ensure the referential integrity between one or two tables. The foreign key is built on the reference relationship between two fields of one table or two fields of two tables.

The order of creating foreign key constraints is to first define the primary key of the master table, and then define the foreign key of the slave table. That is to say, only the primary key of the primary table can be used as a foreign key by the secondary table. The columns in the constrained secondary table may not be the primary key. The primary table limits the update and insertion operations of the secondary table.

5. Default value constraint (Default)

If a default value constraint is defined in the table, when the user inserts a new data row, if the If the row does not specify data, the system assigns the default value to the column. If we do not set the default value, the system defaults to NULL.

6. Auto-increment constraint (AUTO_INCREMENT)

Auto-increment constraint (AUTO_INCREMENT) can constrain any field, which is not necessarily a PRIMARY KEY field. In other words, the auto-incremented field is not equal to the primary key field.

But the primary key field of the PRIMARY_KEY constraint must be an auto-increment field, that is, PRIMARY_KEY and AUTO_INCREMENT must act on the same field.

When inserting the first record, the auto-increment field is not given a specific value and can be written as DEFAULT/NULL. Then when the field is inserted in the future, the auto-increment field starts from 1 and no record is inserted. , the value of this auto-increment field increases by 1. When the first record is inserted, a specific value is given to the auto-increment field. Then the value of the auto-increment field in the records inserted thereafter will increase by 1 each time based on the value of the auto-increment field in the first record.

You can also specify the remaining fields to insert the record without specifying the auto-increment field when inserting the record.

The above is the detailed content of What are the common constraints in mysql?. For more information, please follow other related articles on the PHP Chinese website!