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 );