Data constraint examples based on MySQL database and introduction to five integrity constraints

#Five integrity constraints:
#NOT NULL: Not null constraint, specifies that a column cannot be empty;
#UNIQUE: unique constraint, specifies that a column or a combination of columns cannot be repeated #PRIMARY KEY: primary key, specifies that the value of this column can uniquely identify the record in this column #FOREIGN KEY: foreign key, specifies that this row record belongs to a record in the primary table, mainly used for referential integrity #CHECK: check, specify a Boolean expression to specify that the corresponding value must satisfy the expression (mysql does not support check constraints)
#--------------------------------NOT NULL non-empty constraint---------------------------
create table test4
(
  #Create a non-null constraint id int not null,
name varchar(55) default 'ABCD' not null,
#The default value is null
age int null
);
#Cancel the non-empty constraint alter table test4
 modify name varchar(55) default 'ABCD' not null,
#Add non-empty constraint alter table test4
 modify age int not null;
#--------------------------------UNIQUE: unique constraint--------------------------------
#Column-level constraint syntax to create constraints create table test_unique
 (
 #Create a row-level unique constraint id int not null unique,
 age int
 );
 #Table-level constraint syntax format create table unique_test3
 (
test6_id int not null,
test6_name varchar(255),
test6_pass varchar(255),
#Use table-level constraint syntax to create a unique constraint, specifying that the combination of test6_id and test6_name columns cannot be repeated constraint test6_unique unique(test6_id,test6_name),
#Use table-level constraint syntax to create a unique constraint. The constraint name is test6_unique_2. test6_pass cannot be repeated. constraint test6_unique_2 unique(test6_pass)
 );
 #add keyword to add unique constraint alter table test4
 add unique(id,name,age);
 #modify keyword to delete or add unique constraint alter table test4
 modify age varchar(255) not null;
 alter table test4
 modify age varchar(255) not null unique;
 #For most databases, to delete a constraint, use: alter table table name drop constraint constraint name #However, MySQL does not use this method. Instead, use: alter table table name drop index constraint name #--------------------------------PRIMARY KEY: Primary key constraint--------------------------------
 #The primary key constraint is equivalent to the not-null constraint and the unique constraint.
 #Each table is allowed to have only one primary key, but this primary key can be composed of multiple data columns, and these column combinations cannot be repeated #Standard SQL allows you to name the primary key yourself, but for MySQL, your own name has no effect and is always named PRIMARY by default
 create table primary_test
 (
#Use column-level syntax to create a primary key constraint test_id int primary key,
test_name varchar(255)
 );
 #Use table-level syntax to create a primary key constraint create table primary_test2
 (
test_id int not null,
test_name varchar(255),
test_pass varchar(255),
#Specify the primary key constraint name test2_pk, which is valid for most databases, but not for mysql. The primary key constraint name is still PRIMARY
constraint test2_pk primary key (test_id)
 );
 #Create a primary key with multiple columns create table primary_test3
 (
test_id int,
test_name varchar(255),
primary key(test_id,test_name)
 );
 #Use column-level constraint syntax alter table primary_test3
 modify test_id int primary key();
 #Use table-level constraint syntax alter table primary_test3
 add primary key(test_id,test_name);
 #Delete the primary key constraint: alter table table name drop primary key;
 #Self-increment feature of primary key column: If the data column type is integer and the column is used as the primary key column, you can specify that the column has the self-increment function. #MySQL uses auto_increment to set self-increment. When inserting records into the table, you do not need to specify a value for the column. The system generates a value for the column. create table primary_test3
 (
//Create primary key constraint, set auto-increment test_id int auto_increment primary key,
test_name varchar(255)
 );
 #Foreign key constraint FOREIGN KEY
 #In MySQL, only foreign key constraints created with table-level syntax can take effect. #To ensure the existence of the reference master table, first create the master table create table teacher_tb
 (
t_id int auto_increment,
t_name varchar(255),
primary key(t_id)
 );
 create table student_tb
 (
s_id int auto_increment primary key,
s_name varchar(255) not null,
t_java int,
foreign key(t_java) references teacher_tb(t_id)
 );
#If you use table-level constraint syntax, you need to use foreign key to specify the foreign key column of this table. If you do not specify the constraint name when creating a foreign key constraint,
#MySQL will name the foreign key constraint table_name_ibfk_n, where table_name is the table name and n is an integer starting from 1. create table teacher_tb2
 (
t_id int auto_increment,
t_name varchar(255),
primary key(t_id)
 );
 create table student_tb2
 (
s_id int auto_increment primary key,
s_name varchar(255) not null,
t_java int,
constraint student_teacher_fk foreign key(t_java) references teacher_tb2(t_id)
 );
 #Establish multi-column composite foreign key constraints create table teacher_tb5
 (
t_name varchar(255),
t_pass varchar(255),
primary key(t_name,t_pass)
 );
 create table student_tb5
 (
s_id int auto_increment primary key,
s_name varchar(255) not null,
t_java_pass varchar(255),
t_java_name varchar(255),
foreign key(t_java_name,t_java_pass) 
  references teacher_tb5(t_name,t_pass)
 );
 #Delete foreign key constraint alter table student_tb2
 drop foreign key student_teacher_fk;
 #Add foreign key constraint alter table student_tb2
 add foreign key(t_java) references teacher_tb2(t_id);
 #Foreign key constraint refers to itself, self-constraint create table foreign_test9
 (
foreign_id int auto_increment primary key,
foreign_name varchar(255),
refer_id int,
foreign key(refer_id) references foreign_test9(foreign_id)
 );
 #Define that when the master table record is deleted, the slave table record will also be deleted #on delete cascade delete all slave table records that refer to the master table record #on delete set null set the slave table records that refer to the master table record to null
 create table teacher_tb8
 (
t_id int auto_increment,
t_name varchar(255),
primary key(t_id)
 );
 create table student_tb8
 (
s_id int auto_increment primary key,
s_name varchar(255) not null,
t_java int,
constraint student_teacher_fk foreign key(t_java) references teacher_tb8(t_id) on delete cascade
 );