On the constraint of data in SQL

Data constraints

-- constrain the data in the User action table

1. Default Value

-- When a user does not insert a value into a field that uses a default value, the default value is used

1) It is possible to insert NULL for the default value field.

2) The default Value field can be inserted non-null

Example:address varchar default ' Guangzhou Tianhe '--defaults '

2, non-empty

-- limit field must be assigned value

1) non-null characters must be assigned

2) non-null characters cannot be assigned null

"Example:gender varchar (2) Not null-- non-empty "

3, the only

-- The value of the field cannot be duplicated

1) Unique field can be inserted null

2) Unique field can insert multiple null

"Example:ID int inique-- unique "

4. PRIMARY Key

-- Unique + non-empty

1) Typically, each table will have a primary key field set. Used to mark the uniqueness of each record in a table.

2) It is recommended that you do not select a field that contains business meaning for the table as the primary key, and it is recommended that you design a non-business-meaning ID field independently for each table.

"Example:ID int primary key-- primary key "

5. self-growth

-- automatic growth of corresponding values

"Example:ID int (4) Zerofill primary key auto_increment-- self-growing, starting from 0 zerofill 0 padding "

6. foreign key -- constrain data for both tables

when the case of two kinds of tables appears when to solving high data redundancy problems will be separate out a sheet

For example:  Employee Table and departmental tables

problem arises : when inserting employee table data, the Department of the employee table The ID field can be inserted arbitrarily!!!!!

solve the problem: use FOREIGN KEY constraint -- Constrain Insert Department ID field value for Employee table ( Add a FOREIGN KEY constraint in the Department ID field of the employee table !!!) )

1) The constrained table is called the secondary table, the table that constrains others is called the main table, and the foreign key is set on the secondary table!!!

2) Main Table Reference field universal primary key!

3) Add data: First add the Main table, then add the secondary table

4) Modify the data: Modify the secondary table first, then modify the main table

5) Delete the data: Delete the secondary table before deleting the main table

" The good Lord comes first, the bad vice watches when "

On the constraint of data in SQL