What are the common constraints in mysql?

The common constraints in mysql are: 1. Primary key; 2. Default value; 3. Unique; 4. Foreign key; 5. Non-null. A constraint is a restriction that ensures the integrity and uniqueness of the table's data by restricting the data in the rows or columns of the table.

# A constraint is a restriction that ensures the integrity and uniqueness of the table's data by restricting the data in the rows or columns of the table.

(Recommended tutorial: mysql tutorial)

Several constraints commonly used in MYSQL:

1. Primary key

Primary key ( PRIMARY KEY) is used to constrain a row in a table. As the identifier of this row, a row can be accurately located in a table through the primary key, so the primary key is very important. The primary key requires that the data in this row cannot be repeated and cannot be empty.

There is also a special primary key-composite primary key. The primary key can not only be one column in the table, but can also be jointly identified by two or more columns in the table.

2. Default value

Default value constraint (DEFAULT) stipulates what to do when the column with DEFAULT constraint inserts empty data.

DEFAULT constraints will only be reflected when using the INSERT statement (introduced in the previous experiment). In the INSERT statement, if the position constrained by DEFAULT has no value, then this position will be filled with the value of DEFAULT.

3. Unique constraint

The unique constraint (UNIQUE) is relatively simple. It stipulates that the value of a specified column in a table must not have duplicate values, that is, each value in this column is unique.

When the newly inserted data in the INSERT statement duplicates the existing data, if there is a UNIQUE constraint, the INSERT fails.

4. Foreign key

Foreign key (FOREIGN KEY) can not only ensure data integrity, but also express the relationship between tables.

A table can have multiple foreign keys. Each foreign key must REFERENCES (reference) the primary key of another table. The value of the column constrained by the foreign key must have a corresponding value in the column it refers to.

During INSERT, if the value constrained by the foreign key does not correspond to the reference column, for example, in the following command, there is no dpt3 in the reference column (dpt_name of the department table), then INSERT fails

5. Non-null

NOT NULL constraint (NOT NULL), you can understand by the name. The column constrained by non-null must be non-null when inserting a value.

If a non-null constraint is violated in MySQL, no error will be reported, only a warning.

Related recommendations: php training

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!