MySQL's Constraints

In MySQL, the constraint is stored in the table_constraints of the INFORMATION_SCHEMA database, and the constraint information can be queried by this table.

The constraint mainly completes the examination to the data, guarantees the integrity of the database data, and ensures that the data is not deleted if there is mutual dependent data.

Five types of constraints are commonly used:
NOT NULL: non-null constraint, specifying that a column is not empty
Unique: Unique constraint that specifies that data for a column and a combination of columns cannot be duplicated
Primary key: PRIMARY KEY constraint, specifying that the data for a column cannot be duplicated, unique
FOREIGN key: Foreign key, which specifies that the column record belongs to a record in the primary table, referencing another piece of data
Check: Checks, specifies an expression that verifies the specified data
Note: MySQL does not support check constraints, but you can use check constraints without any effect;
Depending on the constraint data column limits, constraints can be divided into:
Single-column constraint: Each constraint is constrained to only one row
Multi-column constraints: Each constraint constrains multiple columns of data

1, NOT NULL

A non-null constraint is used to ensure that the value of the top column is not NULL, and the non-null constraint can only appear on the columns of the Table object.
Null type characteristics: All types of values can be null, including int, float, and other data types the empty string "" is not equal to null,0 and not equal to NULL
CREATE TABLE Temp (
ID int NOT NULL,
Name varchar (255) NOT NULL default ' ABC ',
Sex char null
)
The table above adds a non-null constraint, or you can modify or add a non-null constraint with ALTER
Adding non-null constraints
ALTER TABLE temp Modify sex varchar (2) not null;
Cancel a non-null constraint
ALTER TABLE temp Modify sex varchar (2) null;
Cancel non-null constraint, increase default value
ALTER TABLE temp Modify sex varchar (2) Default ' ABC ';

2. Unique

The only constraint is that the column or combination of columns of the specified table cannot be duplicated, guaranteeing the uniqueness of the data. Although a unique constraint does not allow duplicate values, it can be more than one null, and the same table can have multiple unique constraints, multiple combinations of columns. When you create a unique constraint, the default and column names are the same if you do not give a unique constraint name. MySQL will create a unique index on the column of the unique constraint by default;
CREATE TABLE Temp (
ID int NOT NULL,
Name varchar (25),
Password varchar (16),
Constraint uk_name_pwd unique (name, password)
);
Indicates that the user name and password combination cannot be duplicated
Add a UNIQUE Constraint
ALTER TABLE temp Add unique (name, password);
Modify Uniqueness Constraints
ALTER TABLE TEMP Modify name varchar (+) unique;
Delete Constraint
ALTER TABLE temp DROP INDEX name;

3. Primary key

A PRIMARY KEY constraint is equivalent to a combination of a unique constraint + non-null constraint, a PRIMARY KEY constraint column does not allow duplicates, and null values are not allowed, and if the combination of multiple columns is a PRIMARY KEY constraint, the columns are not allowed to be null, and the combined values do not allow duplicates. Only one primary key is allowed per table, and a PRIMARY key constraint can be created at the column level or at the table level.
MySQL's primary key name is always primary, and when a primary key constraint is created, the system defaults to establishing a unique index on the columns column combination.
Column mode:
CREATE TABLE Temp (
ID int PRIMARY KEY,
Name varchar (25)
);
CREATE TABLE Temp2 (
ID int NOT NULL,
Name varchar (25),
PWD varchar (15),
Constraint pk_temp_id primary key (ID)
);
Combination mode:
CREATE TABLE Temp2 (
ID int NOT NULL,
Name varchar (25),
PWD varchar (15),
Constraint pk_temp_id primary key (name, PWD)
);
ALTER DELETE PRIMARY KEY constraint
ALTER TABLE temp drop PRIMARY key;
Alter ADD PRIMARY KEY
ALTER TABLE TEMP Add primary key (name, PWD);
Alter modifies column as primary key
ALTER TABLE temp Modify ID int primary key;
Set Primary key auto-increment
CREATE TABLE Temp (
ID int auto_increment PRIMARY key,
Name varchar (20),
PWD varchar (16)
);
Auto_increment self-increment mode, when the data is inserted, it is not necessary to insert a value into the column.

4. Foreign key

FOREIGN KEY constraints are guaranteed referential integrity between one or two tables, which is a reference relationship between two fields built into a table or two fields of two tables. That is, the foreign key value from the table must be found or empty in the primary table. When the primary table's records are referenced from a table, the records of the primary table are not allowed to be deleted, and if you want to delete the data, you need to delete the data from the table that relies on that record before you can delete the data from the primary table. There is also a cascade to delete the child table data.
Note: The reference column of a foreign KEY constraint, the column referenced in the primary table that can only be a primary KEY or UNIQUE key constraint, assuming that the referenced primary table column is not a unique record, the data referenced from the table is not determined by the location of the record. The same table can have more than one foreign key constraint.
To create a FOREIGN KEY constraint:
Main Table
CREATE TABLE Classes (
ID int auto_increment PRIMARY key,
Name varchar (20)
);
From the table
CREATE TABLE Student (
ID int auto_increment,
Name varchar (22),
Constraint pk_id primary key (ID),
classes_id int references classes (ID)
);
The table is usually built first, and then from the table, so that the tables referenced from the table are present.
Create a FOREIGN KEY constraint at the table level:
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (25),
classes_id int,
Foreign KEY (classes_id) references classes (ID)
);
The above method of creating a foreign key does not specify a constraint name, the system assigns a FOREIGN KEY constraint name to the FOREIGN KEY constraint by default, named Student_ibfk_n, where student is the table name and N is an integer starting at 1 for the current constraint.
Specify the constraint name:
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (25),
classes_id int,
Constraint fk_classes_id foreign KEY (classes_id) references classes (ID)
);
For multi-column foreign key combinations, you must use the table-level constraint syntax:
CREATE TABLE Classes (
ID int,
Name varchar (20),
Number int,
Primary key (name, number)
);
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (20),
Classes_name varchar (20),
Classes_number int,
Foreign key (Classes_name, Classes_number) references classes (name, number)
);
To delete a foreign KEY constraint:
ALTER TABLE student drop foreign key student_ibfk_1;
ALTER TABLE student drop foreign key fk_student_id;
To add a FOREIGN KEY constraint:
ALTER TABLE Student Add foreign key (Classes_name, Classes_number) references classes (name, number);
Self-referencing, self-correlating (recursive table, tree-like table)
CREATE TABLE Tree (
ID int auto_increment PRIMARY key,
Name varchar (50),
parent_id int,
Foreign KEY (parent_id) references tree (ID)
);
Cascade Delete: When you delete data from the primary table, the associated data is also deleted from the table, you need to add the on DELETE cascade or the on delete set NULL after the FOREIGN KEY constraint is established, which is a cascade delete, which sets the value of the associated column from the table to null.
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (20),
Classes_name varchar (20),
Classes_number int,/* table-level Union foreign KEY */
Foreign key (Classes_name, Classes_number) references classes (name, number) on DELETE cascade
);

5. Check

MySQL can use check constraints, but check constraints have no effect on data validation.
CREATE TABLE Temp (
ID int auto_increment,
Name varchar (20),
Age int,
Primary key (ID),
Check (age > 20)
);
The check constraint above requires that age must be greater than 20, but has no effect. However, there are no errors or warnings when creating the table.

MySQL's Constraints