What are the five types of integrity constraints? Data constraints based on the MySQL database instance, integrity mysql

What are the five types of integrity constraints? Data constraints based on the MySQL database instance, integrity mysql
To prevent non-conforming data from entering the database, DBMS automatically monitors the data according to certain constraints when you insert, modify, or delete the data, make the non-compliant data unable to enter the database to ensure that the data stored in the database is correct, valid, and compatible.
# Data constraints

# Five integrity constraints: # not null: non-NULL constraint. Specify a column to be empty. # UNIQUE: UNIQUE constraint. Specify a column or a combination of multiple columns. # primary key: primary KEY, specifying the column value can uniquely identify the Column Record # foreign key: foreign key, specifying that the row record belongs to a record in the main table, mainly used for reference integrity # CHECK: check, specify a Boolean expression, used to specify that the corresponding value must meet this expression (mysql does not support check constraints) # -------------------------------- not null non-null constraint ------------------------- create table test4 (# create a non-null constraint id int not NULL, name varchar (55) default 'abcd' not null, # The default value is nullage int null); # cancel the non-null constraint alter table tes T4 modify name varchar (55) default 'abcd' not null, # Add a non-null constraint alter table test4 modify age int not null; # ---------------------------- UNIQUE: unique constraint -------------------------------- # create table test_unique (# create a row-level unique constraint: id int not null unique, age int ); # create table unique_test3 (test6_id int not null, test6_name varchar (255), test6_pass varchar (255), # use the table-level constraint syntax to create a unique constraint, specify test6_id and tes The combination of t6_name and two columns cannot duplicate constraint test6_unique unique (test6_id, test6_name). # use the table-level constraint syntax to create a unique constraint. The constraint name is constraint. test6_pass cannot duplicate constraint test6_unique_2 unique (Test )); # The add keyword adds the unique constraint alter table test4 add unique (id, name, age); # The modify keyword deletes or adds the unique constraint alter table test4 modify age varchar (255) not null; alter table test4 modify age varchar (255) not null unique; # For most databases, delete constraints: alter table Name drop constrai Nt constraint name # But Mysql does not adopt this method, but: alter table Name drop index constraint name # ---------------------------- primary key: primary key constraint ------------------------------ # primary key constraint is equivalent to non-null and unique constraints. # Each table can have only one primary key, but this primary key can be composed of multiple data columns. The combination of these columns cannot be repeated # standard SQL allows the primary key to be named by itself, however, Mysql has no effect on its own name. It is always named PRIMARY create table primary_test by default (# Use the column-level syntax to create a primary key constraint test_id int PRIMARY key, test_name varchar (255 )); # create table primary_test2 (test_id int not null, test_name varchar (255), test_pass varchar (255) using table-level syntax, # specify the primary key constraint name test2_pk, valid for most databases, but not for mysql. The primary key constraint name is still PRIMARYconstraint test2_pk primary key (test_id); # create a primary key create table primary_test3 (test_id int, test_name varchar (255), primary key (test_id, test_name); # Use the column-level constraint syntax alter table primary_test3 modify test_id int primary key (); # use the table-level constraint syntax alter table primary_test3 add primary key (test_id, test_name); # Delete the primary key constraint: alter table Name drop primary key; # primary key column auto-increment feature: if a data column is of an integer type and acts as the primary key column, you can specify that the column has the auto-growth function # mysql uses auto_increment to set auto-growth, when a record is inserted into the table, the system generates the create table primary_test3 (// sets the primary key constraint and sets the auto-increment test_id int auto_increment primary key, test_name varchar (255); # foreign key constraint foreign key # Only foreign key constraints created by table-level syntax in Mysql can take effect # to ensure the existence of the reference base 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 the table, if no constraint name is specified when a foreign key constraint is created, # mysql will name the foreign key constraint table_name_ibfk_n, where table_name is the name of the slave table, n is the integer 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 )); # 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); # alter table student_tb2 drop foreign key student_teacher_fk; # add alter table student_tb2 add foreign key (t_java) references teacher_tb2 (t_id); # foreign key constraints refer to their own, self-constraint create table foreign_test9 (foreign_id int auto_increment primary key, foreign_name varchar (255), refer_id int, foreign key) references foreign_test9 (foreign_id); # defines when a primary table record is deleted, the slave table record is also deleted # on delete cascade deletes all slave table records that reference the master table record in cascade # on delete set null transfers the slave table records that reference the master table record from table is set to null e 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 );