Technology Sharing

MySQL Quick Mastery (Part 2) Constraints and Strategies

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Focus on a practical

This article is mainly about application, specific sql operation statement self-check

1. Primary key constraint

The primary key is used to uniquely identify a record. Each table can only have one primary key (multiple fields are joint primary keys)

Primary key column characteristics:非空唯一

  1. existNavicatAdd a primary key

If you don't set a primary key when creating a new table, it will fail. This golden key is the identifier of the primary key.

insert image description here

  1. Primary key auto-increment strategy

Select the primary key and add it. The auto-increment strategy can automatically generate a unique serial number in sequence.

insert image description here

  1. Truncate Table

Truncate TabletruncateWith clear tabledelete的区别🎈
Truncate table: keep the table structure and delete everything else.自增字段会回到默认值开始
Clear table: keep the table structure and do not release space.自增字段不会重置

2. Non-null constraints

Adding a Not Null Constraint

insert image description here

3. Unique constraint

Adding a unique constraint
insert image description here

insert image description here

4. Default constraints

This is the default value. If it is not filled in, this value will be used by default.

insert image description here

insert image description here

5. Foreign key constraints

  1. Disadvantages of single table

Most of the data content is repeated, and when modifying it, you need to traverse all the content to modify it. For example, the community can be divided into a separate table, and only its id needs to be recorded in the original table.

  1. Foreign key specifications

要求:The data type and length of the foreign key column must be consistent with the data type and length of the referenced primary key column.

  1. How to add a foreign key

The primary table is the main table, and vice versa.

外键在从表添加

Here we take cascading update and delete as an example, but there are more options than this one.

insert image description here

  1. Update deletion policy

CASCADE: Delete or update from the parent table and automatically delete or update matching rows in the child table
SET NULL: Delete or update rows from the parent table and set the foreign key columns in the child table to NULL. If you use this option, you must ensure that the child table columns do not specify NOT NULL
RESTRICT: Deny delete or update operations on the parent table
NO ACTION: A standard SQL keyword that is equivalent to RESTRICT in MySQL.