1, Introduction

• Why constraints

Data Integrity refers to the Accuracy and Reliability of data. It is proposed to prevent the existence of data that does not comply with the semantic provisions in the database and to prevent invalid operation or error information caused by the input and output of error information.

1. Entity Integrity: for example, two identical and indistinguishable records cannot exist in the same table
2. Domain Integrity: for example, the age range is 0-120 and the gender range is "male / female"
3. Referential Integrity: for example, the employee's department should be found in the Department table
4. User defined integrity: for example, if the user name is unique and the password cannot be blank, the salary of the manager of the Department shall not be higher than 5 times of the average salary of the employees of the Department.

• View the existing constraints of a table

# information_schema database name (System Library)
# table_constraints table name (dedicated to storing constraints for each table)
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'Table name';

2, Foreign key constraint

2.1 description

Used to limit the referential integrity of a field in a table. In MySQL, foreign key constraints are costly and consume system resources. For large concurrent SQL operations, it may not be suitable.

• Reference description

1. Primary table (parent table): referenced table, referenced table
2. From table (sub table): refer to other people's table and refer to other people's table

• characteristic

1. The foreign key column of the slave table must reference / refer to the primary key of the master table or the column with unique constraints
2. When the records of the master table are referenced by the slave table, if the master table wants to delete the data, the data that depends on the record in the slave table must be deleted first, and then the data of the master table can be deleted
3. The name of the foreign key column of the slave table and the referenced column of the master table can be different, but the data type must be the same and the logical meaning must be the same
4. When creating a foreign key constraint, the system will establish a corresponding common index on the column by default
5. After you delete a foreign key constraint, you must manually delete the corresponding index

2.2 using foreign keys

• Add foreign key constraint

1. When creating tables

create table Main table name(
  Field 1 data type primary key,
  Field 2 data type
);

create table From table name(
  Field 1 data type primary key,
  Field 2 data type,
  [CONSTRAINT <Foreign key constraint name>] FOREIGN KEY(From a field in the table) references Main table name(Referenced field)
);

create table dept( #Main table
  did int primary key, #Department number
  dname varchar(50) #Department name
);

create table emp(#From table
  eid int primary key, #Employee number
  ename varchar(5), #Employee name
  deptid int, #Employee's Department
  foreign key (deptid) references dept(did) #Specify foreign key constraints in the from table
  # The data types of deptid in emp table and did in dept table are the same, and the meaning is to represent the department number
);

1. After table building

ALTER TABLE From table name ADD [CONSTRAINT Constraint name] FOREIGN KEY (Fields from table) 
REFERENCES Main table name(Referenced field) [on update xx][on delete xx];

ALTER TABLE emp1 ADD [CONSTRAINT emp_dept_id_fk] 
FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

• Constraint relation

1. The binding relationship is for both parties
2. After the foreign key constraint is added, the modification and deletion data of the main table are constrained
3. After adding a foreign key constraint, the data added and modified from the table is constrained
4. When deleting a primary table, you need to delete the secondary table first, or delete the relationship that references the primary table from the foreign key in the table first

• Delete foreign key constraint

# (1) The first step is to view the constraint name and delete the foreign key constraint
SELECT * FROM information_schema.table_constraints WHERE table_name = 'Table name';#View the constraint name of a table
ALTER TABLE From table name DROP FOREIGN KEY Foreign key constraint name;

#(2) The second step is to view the index name and delete the index. (note that it can only be deleted manually)
SHOW INDEX FROM Table name; #View the index name of a table
ALTER TABLE From table name DROP INDEX Index name;

2.3 restraint level

If no grade is specified, it is equivalent to Restrict.

Constraint level	explain
Cascade	When updating / deleting records on the parent table, update/delete the matching records of the child table synchronously
Set null	When updating / deleting records on the parent table, set the column of matching records on the child table to null, but note that the foreign key column of the child table cannot be not null
No action	If there are matching records in the child table, the update/delete operation on the candidate key corresponding to the parent table is not allowed
Restrict	The same as no action is to check the foreign key constraint immediately
Set default	When the parent table changes, the child table sets the foreign key column to a default value, but Innodb cannot recognize it (blank may be displayed in the visualizer SQLyog)

For foreign key constraints, it is best to use the method of ON UPDATE CASCADE ON DELETE RESTRICT, that is, the master table is updated when it is updated. If there are still reference records in the slave table, the master table cannot be deleted.

3, Other constraints

3.1 non empty constraints

The empty string '' is not equal to NULL, nor is 0 equal to NULL.

• Add non empty constraint

# When creating tables
CREATE TABLE Table name(
  Field name data type,
  Field name data type NOT NULL,
  Field name data type NOT NULL
);

# After table building
alter table Table name modify Field name data type not null;
ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;

• Delete non empty constraints

# Removing not null is equivalent to modifying a non annotation field, which can be empty
alter table Table name modify Field name data type;

ALTER TABLE emp MODIFY sex VARCHAR(30);

3.2 uniqueness constraints

When creating a unique constraint, if the unique constraint is not named, it will be the same as the column name by default.

• Add uniqueness constraint

1. When creating tables

CREATE TABLE Table name(
  Field name data type,
  Field name data type UNIQUE, # Column level mode
  Field name data type
);

CREATE TABLE Table name(
  Field name data type,
  Field name data type,
  Field name data type,
  UNIQUE KEY `key_name` (`field_list`]) # Table level mode (when the length of the field list is > 1, it constitutes a compound unique constraint, that is, the combination of multiple fields is unique)
);

1. After table building

alter table Table name modify Field name field type unique; # Column level mode

alter table Table name add unique key(Field list); # Table level mode

• Delete uniqueness constraint

# If unique is not specified when adding a uniqueness constraint_ key_ Name defaults to the field name
ALTER TABLE USER
DROP INDEX unique_key_name;

3.3 primary key constraints

The primary key constraint is equivalent to the combination of unique constraint and non empty constraint. The primary key constraint column cannot be duplicated or null value is not allowed.

• Add primary key constraint

1. When creating tables

create table Table name(
  Field name data type primary key, # Column level mode
  Field name data type,
  Field name data type
);

create table Table name(
  Field name data type,
  Field name data type,
  Field name data type,
  [constraint Constraint name] primary key(Field list) # Table level mode (the field list can be one field or multiple fields. If it is multiple fields, it is a composite primary key)
);

1. After table building

ALTER TABLE Table name ADD PRIMARY KEY(Field list);

• Delete primary key constraint

alter table Table name drop primary key;

3.4 self increasing constraint

• explain

1. A table can have at most one self growing column
2. The column of self growth column constraint must be a key column (primary key column, unique key column)
3. The data type of a column with a self incrementing constraint must be an integer type
4. If 0 and null are specified in the auto increment column, it will auto increment based on the current maximum value; If the auto increment column manually specifies a specific value, it is directly assigned as a specific value.

• Specify self increasing constraints

1. Before table building

create table Table name(
  Field name data type primary key auto_increment,
  Field name data type unique key not null,
  Field name data type unique key,
  Field name data type not null default Default value,
);

create table Table name(
  Field name data type default Default value ,
  Field name data type unique key auto_increment,
  Field name data type not null default Default value,,
  primary key(Field name)
);

1. After table building

alter table Table name modify Field name data type auto_increment;

• Delete autoincrement constraint

alter table Table name modify Field name data type; # Remove auto_increment is equivalent to deletion

3.5 check constraints

Check whether the value of a field is required by symbol xx, which generally refers to the range of values

mysql> CREATE TABLE employee(
    ->   eid INT PRIMARY KEY,
    ->   ename VARCHAR(20),
    ->   gender VARCHAR(20) CHECK(gender IN ('male', 'female')),
    ->   age TINYINT CHECK(0 <= age <= 150)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `eid` int NOT NULL,
  `ename` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `gender` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` tinyint DEFAULT NULL,
  PRIMARY KEY (`eid`),
  CONSTRAINT `employee_chk_1` CHECK ((`gender` in (_latin1'male',_latin1'female'))),
  CONSTRAINT `employee_chk_2` CHECK (((0 <= `age`) <= 150))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> INSERT INTO employee VALUES (1, 'rayslee', 'male', 100), (2, 'monica', 'female', 18);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO employee VALUES (1, 'joshua', 'secret', 200);
ERROR 1264 (22003): Out of range value for column 'age' at row 1

mysql> INSERT INTO employee VALUES (1, 'joshua', 'secret', 100);
ERROR 3819 (HY000): Check constraint 'employee_chk_1' is violated.
mysql> 

3.6 default values

Assign a default value to a field / column. Once the default value is set, when inserting data, if this field has no explicit assignment, it will be assigned as the default value.

• Add default values for fields

1. When creating tables

# Note: the default value constraint is generally not added to the unique key and primary key column
create table Table name(
  Field name data type primary key,
  Field name data type unique key not null,
  Field name data type unique key,
  Field name data type not null default Default value,
);

1. After table building

alter table Table name modify Field name data type default Default value;

# If this field has a non empty constraint and you still keep the non empty constraint, you have to keep the non empty constraint when adding the default value constraint, otherwise the non empty constraint will be deleted
# If you want to delete a non modify constraint, the default value will be added to the constraint. Otherwise, if you want to delete a non modify constraint, the same will be true
alter table Table name modify Field name data type default Default value not null;

• Delete default constraint

alter table Table name modify Field name data type ;#Delete the default value constraint and leave the non empty constraint

alter table Table name modify Field name data type not null; #Delete the default value constraint and keep the non empty constraint