Data constraints and SQL statement constraints for SQL statements

Data constraints and SQL statement constraints for SQL statements
Data constraints of SQL statements

 

What is data constraints?

Data constraints are used to restrict unauthorized data modification operations.

 

1. Default Value of the constraint Field

If a field is not assigned a value when a record is inserted, we can set its default value.

Keyword: default

Create table test1 (tin INT, tname VARCHAR (20) DEFAULT 'tb'); insert into test1 (tin) VALUES (1); SELECT * FROM test1;

 

2. Non-empty Constraint

Constrain a field so that its value cannot be blank

Keyword: not null

CREATE TABLE test1(tin INT,tname VARCHAR(20) NOT NULL);

Here, non-null indicates null and no inserted value. If it is a null string, it can be inserted.

 

3. constraint duplicate value

So that the values of different record fields cannot be the same.

Keyword: unique

CREATE TABLE test1(tin INT UNIQUE,tname VARCHAR(20));

Although it cannot be repeated, null can be inserted, and the values of multiple records can be null.

 

4. Primary Key constraints

When each record needs to have a unique identifier, the primary key needs to be used. For example, the ID in life. Generally, the ID of each person in the company is unique and is a unique identifier for each person.

Keyword: primary key

CREATE TABLE test1(tin INT PRIMARY KEY,tname VARCHAR(20));

Primary key features: Unique, cannot be null.

 

5. Self-Growth

Some data we want to insert a record into the table, then the record will automatically get a number, each time a record is inserted, the number will automatically add 1, such as ID, this requires self-growth.

Keyword: auto_increment

CREATE TABLE test1(tin INT PRIMARY KEY AUTO_INCREMENT,tname VARCHAR(20));

Self-increasing features:

We use the delete from statement to delete all data in the table. The auto-increment value is not reset.

If you use the truncate fom statement to delete all data in the table, the auto-increment value starts from 0 again.

TRUNCATE TABLE test;