Constraint is a kind of restriction. It restricts the data of rows or columns of a table to ensure the integrity and uniqueness of the data of the table.

Several common constraints in MYSQL:

1. primary key constraint

The primary key constraint is equivalent to the combination of unique constraint + non empty constraint. The primary key constraint column cannot be duplicate or null.

Each table can only have one primary key at most. A primary key constraint can be created at the column level or at the table level.

When you create a constraint for a primary key, the system will establish a corresponding unique index on the column and column combination by default.

-- Basic mode
create table temp( 
id int primary key,
name varchar(20)
);

-- Combination mode
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);

-- Delete primary key constraint
alter table temp drop primary key;

-- Add primary key constraint
alter table temp add primary key(id,name);

-- Modify primary key constraint
alter  table temp modify id int primary key；

2. foreign key constraint

A foreign key constraint is to ensure the referential integrity between one or two tables. A foreign key is a reference relationship between two fields in a table or between two fields in two tables

-- Basic mode
-- Main table
create table temp(
id int primary key,
name varchar(20)
);

-- Secondary table
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);


-- Table level constraint syntax must be used for multi column foreign key combinations
-- Main table
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);

-- Secondary table
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*Table level federated foreign key*/
foreign key(classes_name, classes_number) references classes(name, number) 
);


-- Delete foreign key constraint
alter table student drop foreign key student_id;


-- Add foreign key constraint
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

3. unique constraint

The unique constraint is that the column or column combination of the specified table cannot be repeated to ensure the uniqueness of the data.

Unique constraints do not allow duplicate values, but can be multiple null s.

The same table can have multiple unique constraints and multiple column combination constraints.

When creating a unique constraint, if you do not give a unique constraint name, it is the same as the column name by default.

Unique constraints can be created not only in one table, but also in multiple tables at the same time.

-- Set when creating a table, indicating that the user name and password cannot be repeated
    create table temp(
    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)
);


-- Add unique constraint
alter table temp add unique (name, password);


-- Modify unique constraints
alter table temp modify name varchar(25) unique;

-- Delete constraint
alter table temp drop index name;

 

4. Non NULL constraint not null and default

Non null constraints are used to ensure that the value of the current column is not null. Non null constraints can only appear on the columns of table objects.

Null type characteristic:

The values of all types can be null, including int, float and other data types

-- establish table Watch, ID Is a non NULL constraint, name Is a non empty constraint and the default value is abc
create table temp(
           id int not null,
           name varchar(255) not null default  'abc',
           sex char null
)；


-- Add non NULL constraint
alter table temp
modify sex varchar(2) not null;

-- Cancel non empty constraint
alter table temp modify sex varchar(2) null;

-- Cancel the non empty constraint and increase the default value
alter table temp modify sex varchar(2) default 'abc' null;

5. Check constraints

Check whether a field meets the requirements, generally the range of values

create table Table name(
gender varchar(1) check('male' or 'female')
age int(3) check(age < 130 and age > 0)
);