SQL data constraints

SQL data constraints

Data constraints

--Restrict data in user operation tables

 

1,Default Value

-- When you do not insert a value for a field that uses the default value, the default value is used.

1) insert null to the default field.

2) non-null values can be inserted for default fields.

[Example: address varchar (20) default 'guangzhou Tianhe '-- default value]

 

2,Not empty

-- The restricted field must be assigned a value.

1) non-null characters must be assigned a value.

2) non-null characters cannot be null

[Example: gender varchar (2) not null -- not empty]

 

3,Unique

-- The field values cannot be repeated.

1) null can be inserted for unique fields.

2) unique fields can be inserted with multiple null values.

[Example: id int inique -- Unique]

 

4,Primary Key

-- Unique + non-empty

1) generally, a primary key field is set for each table. Used to mark the uniqueness of each record in the table.

2) We recommend that you do not select a field containing the business meaning of the table as the primary key. We recommend that you design an id field independent of the business meaning for each table.

[Example: id int primary key -- primary key]

 

5,Self-Growth

-- The corresponding value automatically increases

[Example: id int (4) zerofill primary key auto_increment -- auto-increment, zero-padding from 0]

 

6. Foreign keys -- Constrain the data of the two tables

When two tables exist, an independent table is generated to solve the problem of high data redundancy.

Example: employee table and department table

Problem: when inserting employee table data, the employee table's department ID field can be inserted as needed !!!!!

Solve the problem: use the foreign key constraint -- constraint to insert the Department ID field value of the employee table (add a foreign key constraint in the department ID field of the employee table !!!)

1) The restricted table is called a sub-table. The tables that constrain others are called the primary table. The foreign key is set to the sub-table !!!

2) The reference fields of the primary table are generally primary keys!

3) add data: Add a primary table and a secondary table.

4) modify data: First modify the sub-table, then modify the master table

5) delete data: First Delete the sub-table and then delete the master table.

【A good thing comes first.]