MySQL Learning (MU lesson Note 5) constraints

Constraint type:

1, NOT NULL (non-null constraint)

2. PRIMARY key (primary KEY constraint)

Only one primary key can exist per data table

The primary key guarantees the uniqueness of the record

Primary key is not NULL automatically

(Auto_increment automatic coding)

3. Unique KEY (single constraint)

Unique constraints guarantee the uniqueness of records

A field with a unique constraint can be a null value

Multiple unique constraints can exist for each data table

When you insert the same content into a username field with a unique constraint, the system prompts an error

Default constraint

Add a DEFAULT constraint to the Sex field

When the sex field is empty, the system records it as 3 by default

FOREIGN key (FOREIGN KEY constraint)

Maintain data consistency, integrity

Implementing one-to-one or one-to-many relationships

MySQL configuration file

Default_storage-engine=innodb

Create a parent table

stored in InnoDB

SHOW CREATE TABLE Province;

Create child table

FOREIGN KEY (PID) REFERENCES prvince (ID)

Parent table Reference column does not have an index created

If no index exists for the foreign key, MySQL will automatically create

Referential actions for FOREIGN KEY constraints

Cascade Delete or update from the parent table and automatically delete or update matching rows in the child table

Set NULL deletes or updates rows from the parent table and sets the foreign key column in the child table to null

Restrict deny Delete or update to parent table

No action standard SQL keyword, same as restrict in MySQL

To add a parent table to see the changes in the child table

Delete records from id=3 in Main Table province

The record of PID 3 in child table User1 is also deleted

A constraint established on a data column, called a column-level constraint

Constraints that are established on multiple data columns, called Table-level constraints

MySQL Learning (MU lesson Note 5) constraints