In order to prevent non-standard data from being stored in the database, MySQL provides a mechanism to check whether the data in the database meets the specified conditions when the user inserts, modifies, or deletes the data, so as to ensure the accuracy of the data in the database. Integrity and consistency, this mechanism is the integrity constraint.

  The following integrity constraints are mainly supported in MySQL, as shown in the table. Where Check constraint is the support provided in MySQL8.

1. Integrity constraint classification

Restrictions	Constraint description
PRIMARY KEY	Primary key constraint, the value of the constraint field can uniquely identify the corresponding record
NOT NULL	Not null constraint, the value of the constraint field cannot be null
UNIQUE	Unique constraint, the value of the constraint field is unique
CHECK	Check constraints to limit the value range of a field
DEFAULT	Default value constraint, the default value of the constraint field
AUTO_INCREMENT	Automatically increase the constraint, the value of the constraint field is automatically incremented
FOREIGN KEY	Foreign key constraints, which constrain the relationship between tables and tables

PRIMARY KEY 	##Primary key constraint, the value of the constraint field can uniquely identify the corresponding record

NOT NULL 	##Not null constraint, the value of the constraint field cannot be null

UNIQUE 	##Unique constraint, the value of the constraint field is unique

CHECK 	##Check constraints to limit the value range of a field

DEFAULT 	##Default value constraint, the default value of the constraint field

AUTO_INCREMENT 	##Automatically increase the constraint, the value of the constraint field is automatically incremented

FOREIGN KEY 	##Foreign key constraints, which constrain the relationship between tables and tables

1. Primary key constraints

The primary key constraint (PRIMARY KEY, abbreviated PK) is the most important constraint in the database, and its role is to constrain a field in the table to uniquely identify a record. Therefore, using primary key constraints can quickly look up records in a table.

ID card, student ID, etc., the value of the field set as the primary key cannot be repeated (unique), nor can it be empty (non-empty), otherwise a record cannot be uniquely identified.

A primary key can be a single field or a combination of multiple fields. For the addition of a single-field primary key, table-level constraints or column-level constraints can be used; while for the addition of multi-field primary keys, only table-level constraints can be used.

2. Not Null Constraint

The not-null constraint (NOT NULL, abbreviated NK) specifies that the value of a specified field in a table cannot be null (NULL). For a field with a non-null constraint set, when the inserted data is NULL, the database will prompt an error, resulting in the data cannot be inserted.

Whether it is a single field or multiple fields, the addition of non-null constraints can only use column-level constraints (non-null constraints have no table-level constraints)

Add a not-null constraint to a field in an existing table

 alter   table student8 modify stu_sex varchar(1) not null;

Drop not-null constraints using the ALTER TABLE statement

 alter  table student8 modify stu_sex varchar(1) null;

3. Unique Constraint

The unique constraint (UNIQUE, abbreviated UK) is relatively simple, it stipulates that the value of a field specified in a table cannot be repeated, that is, each value of this field is unique. If you want the value of a field not to be repeated, you can add a unique constraint for that field.

Column-level constraints and table-level constraints can be used regardless of the addition of single-field or multiple-field unique constraints

4. Check Constraints

Check constraints (CHECK) are used to limit the value range of a field, which can be defined as column-level constraints or table-level constraints. MySQL8 began to support check constraints.

5. Default value constraints

The default value constraint (DEFAULT) is used to specify the default value of the field. If a field that is set as a DEFAULT constraint does not have a specific value inserted, the value of the field will be filled with the default value.

The default value constraints are set like not-null constraints, and only column-level constraints can be used.

6. Field values ​​automatically increase constraints

Self-increment constraints (AUTO_INCREMENT) can automatically increase the value of a field in the table. There can only be one self-incrementing field in a table, and the field must have a constraint defined (the constraint can be a primary key constraint, a unique constraint, and a foreign key constraint). there can be only one auto column and it must be defined as a key" error.

Since auto-increment constraints automatically generate unique ID s, auto-increment constraints are usually used with primary keys and are only applicable to integer types. Under normal circumstances, the value of the auto-increment constraint field will start from 1, and the value of this field will increase by 1 each time a record is added.

Add auto-increment constraints to fields in existing tables

/*Create table student11*/

create table student11 (
       stu_id int(10) primary key,
       stu_name varchar(3),
       stu_sex varchar (1));

/*Add an auto-increment constraint to the primary key field in the student11 table*/

alter   table student11 modify stu_id int(10) auto_increment;

Use the ALTER TABLE statement to remove the auto-increment constraint

alter   table studen11 modify stu_id int(10);

2. Column-level integrity constraints

## non-foreign key constraints


-- column-level integrity constraints
CREATE TABLE t_student(
/*
PRIMARY 
Primary key constraint, the value of the constraint field can uniquely identify the corresponding record AUTO_INCREMENT automatically increases the constraint, and the value of the constraint field automatically increases */
	son	INT(10) PRIMARY KEY auto_increment,  -- 1.Student ID is the primary key 2.If the student number is the primary key, it needs to be incremented
	
/* 
NOT NULL
 Not null constraint, the value of the constraint field cannot be null*/
	sname VARCHAR(10) NOT NULL,  -- Name cannot be empty
	
/*
DEFAULT
-Default value constraint, the default value of the constraint field

CHECK
 Check constraints to limit the value range of a field*/
	sex CHAR(1) DEFAULT 'male' CHECK(sex='male' || sex='Female'),  -- The default gender is male
	
/*CHECK
 Check constraints to limit the value of a field*/
	age INT(3) CHECK(age>=18 and age<=50),  -- Age limit is only 18-540 between the ages
	
	enterdate date,
	classname VARCHAR(10),
	
/*
UNIQUE
 Unique constraint, the value of the constraint field is unique*/
	emile VARCHAR(18) UNIQUE  -- Emily only
);

-- cselect Inquire
SELECT * FROM t_student;


# Add New Data Check Integrity Constraints
INSERT INTO t_student
VALUES(1,'Li Si','male',22,now(),'Chinese Class 1','1234567890@qq.com');

INSERT INTO t_student
VALUES(2,'Li Si','male',22,now(),'Chinese Class 1','1234567891@qq.com');

INSERT INTO t_student(sname,age)
VALUES('Feifei',22);  -- son auto increment

-- delete data
DELETE 
FROM t_student
WHERE son=1;


-- change the data
UPDATE t_student
SET enterdate='2022-5-25',classname='python2 class',emile='12356@qq'
WHERE son =3;

3. Table-level integrity constraints

## table-level integrity constraints constranint constraints

CREATE TABLE t_student1(
	son INT(6),
	sname VARCHAR(5) NOT NULL,
	sex CHAR(1) DEFAULT 'male',
	age INT(3),
	enterdate date,
	classname VARCHAR(10),
	emile VARCHAR(18)
	
/*Add constraints when creating tables
	CONSTRAINT pk_stu  PRIMARY KEY (son),  -- The name of the table-level integrity primary key constraint pk_stu constraint
	CONSTRAINT ck_stu_sex CHECK (sex='male' || sex='female'),
	CONSTRAINT ck_stu_age CHECK (age>=18 and age<=50),
	CONSTRAINT un_stu_emile UNIQUE (emile)
);*/


/*> 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key Time: 0.001s*/
-- The wrong solution is to  AUTO_INCREMENT auto increment remove


-- Add constraints after table creation
ALTER TABLE t_student1 ADD CONSTRAINT pk_stu PRIMARY KEY (son);  -- primary key constraint

ALTER TABLE t_student1 MODIFY son INT(6) auto_increment;  -- Modify the auto-increment condition

ALTER table t_student1 add CONSTRAINT uq_stu_emile UNIQUE (emile);

-- adding data
INSERT INTO t_student1 VALUES(1,'Awei','male', 18, NOW(),'software class', '1234567890@qq.com');


-- View table structure
desc t_student1;

-- View data
SELECT * FROM t_student1;

-- delete table
DROP table t_student1;