MySQL Learning notes (3)

Constraints

Role: Ensure data integrity, uniqueness

column-level constraints (used for 1 fields) based on fields: table-level constraints (used for 2 or more fields)

Constraint type: NOT NULL non-null constraint

PRIMARY Key PRIMARY KEY constraint

Unique KEY Uniqueness Constraint

Default constraint

FOREIGN key FOREIGN KEY constraint

FOREIGN KEY constraints: maintain data consistency integrity for one-to-one or one-to-many relationships

Requirements for creating a FOREIGN KEY constraint:

1. The parent table (the table referenced by the child table) and the child table (the table with the foreign key columns) must have the same storage engine, prohibit the use of temporary tables

2. The storage engine can only be InnoDB

3. Foreign key columns (with foreign key keywords) and reference columns (the column referenced by the foreign key) must have similar data types. the length of the number and whether the sign bit is the same, the character length can be different

4. The Foreign key column and the reference column must create an index . if no index exists for the foreign key column, MySQL will automatically create the index .

The field data type of the foreign key is different from the data type in the referenced table, so an error is made.

The data type length and symbols are the same here, so the foreign key creation is successful.

Referential actions for FOREIGN KEY constraints

CASCADE: Delete or update from parent table and automatically delete or update matching rows in child table

Set NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If you use this option, you must ensure that the child table column does not specify not NULL

RESTRICT: Deny delete or update to parent table

NO ACTION: Same as restrict in MySQL

CASCADE

You must insert a record in the parent table before the child table can insert a record.

← The parent table inserts three provinces data.

← Three records are inserted in the child table, but because one of the insert error but the ID has been self-increment, so no 2

← Delete the id=3 data from the parent table, and then the 3 province record in the child table is missing.

There are few physical constraints in the actual work,

Logic is generally used to constrain.

It is generally defined as a logical foreign key, not a physical foreign key, and is not defined using the keyword foreign key.

Column-level constraints: A constraint created for a column is called a column-level

Table-level constraint: becomes a table-level for 2 or more than 2

Not NULL, DEFAULT has only column-level constraints

MySQL Learning notes (3)