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
|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','email@example.com'); INSERT INTO t_student VALUES(2,'Li Si','male',22,now(),'Chinese Class 1','firstname.lastname@example.org'); 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', 'email@example.com'); -- View table structure desc t_student1; -- View data SELECT * FROM t_student1; -- delete table DROP table t_student1;