Technology Sharing

한어Русский язык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.

2. 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.

3. 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

3. Unique constraint

Adding a unique constraint

4. Default constraints

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

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.

2. 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.

3. 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.

4. 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.