1. Overview of constraints

1.1 why constraints are needed

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.

In order to ensure the integrity of data, SQL specification imposes additional conditions on table data in a constrained manner. Consider from the following four aspects:

• Entity Integrity: for example, two identical and indistinguishable records cannot exist in the same table
• Domain Integrity: for example: age range 0-120, gender range "male / female"
• Referential Integrity: for example, the employee's department should be found in the Department table
• User defined integrity: for example, 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 the average salary of the employees of the Department.

1.2 what are constraints

Constraints are mandatory at the table level.

Constraints can be specified when a table is created (through the CREATE TABLE statement) or after the table is created through the ALTER TABLE statement.

1.3 classification of constraints

• According to the restrictions of constraint data columns, constraints can be divided into:
  • Single column constraint: each constraint constrains only one column
  • Multi column constraint: each constraint can constrain multiple columns of data
• According to the scope of action of constraints, constraints can be divided into:
  • Column level constraint: it can only act on one column, following the definition of the column
  • Table level constraint: it can act on multiple columns, not together with the columns, but defined separately

	position	Supported constraint types	Can I use constraint names
Column level constraints:	After column	Syntax is supported, but foreign keys have no effect	may not
Table level constraints:	Below all columns	Default and non empty are not supported. Others are supported	Yes (primary key has no effect)

• According to the role of constraints, constraints can be divided into:
  • NOT NULL is a non NULL constraint, which specifies that a field cannot be empty
  • UNIQUE constraint specifies that a field is UNIQUE in the whole table
  • PRIMARY KEY (non empty and unique) constraint
  • FOREIGN KEY constraint
  • CHECK check constraints
  • DEFAULT default value constraint

Note: MySQL does not support check constraints, but you can use check constraints without any effect

• 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. Non NULL constraint

2.1 function

Limit the value of a field / column and cannot be empty

2.2 keywords

NOT NULL

2.3 features

• By default, the values of all types can be NULL, including INT, FLOAT and other data types
• Non null constraints can only appear on the columns of table objects. Non null constraints can only be restricted to a column, and non null constraints cannot be combined
• A table can have many columns, which are limited to non empty
• Empty string '' is not equal to NULL, nor is 0 equal to NULL

2.4 adding non empty constraints

(1) When creating tables

CREATE TABLE Table name(
Field name data type,
Field name data type NOT NULL,
Field name data type NOT NULL
);

give an example:

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);

CREATE TABLE student(
sid int,
sname varchar(20) not null,
tel char(11) ,
cardid char(18) not null
);

insert into student values(1,'Zhang San','13710011002','110222198912032545'); #success

insert into student values(2,'Li Si','13710011002',null);#The ID number is empty.
ERROR 1048 (23000): Column 'cardid' cannot be null

insert into student values(2,'Li Si',null,'110222198912032546');#Success, tel can be null

insert into student values(3,null,null,'110222198912032547');#fail
ERROR 1048 (23000): Column 'sname' cannot be null

(2) After table building

alter table Table name modify Field name data type not null;

give an example:

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

alter table student modify sname varchar(20) not null;

2.5 delete non empty constraint

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

give an example:

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' N

3. Uniqueness constraint