What are the five integrity constraints--data constraint instances based on MySQL database

In order to prevent non-conforming data from entering the database, when the user inserts, modifies and deletes the data, the DBMS automatically monitors the data according to certain constraints so that non-conforming data cannot enter the database to ensure that the data stored in the database is correct, effective and compatible.
#Data Constraints

#五种完整性约束: #NOT NULL: A non-null constraint that specifies that a column cannot be empty; #UNIQUE: A unique constraint that specifies that a column or combination of columns cannot be repeated #primary key: A primary key, which specifies that the value of the column uniquely identifies the column record #foreign key: foreign key, Specifies that the row record belongs to a record in the primary table, primarily for referential integrity #check: Check, specifying a Boolean expression that specifies that the corresponding value must satisfy the expression (MySQL does not support CHECK constraints) #----------------------------- ---NOT NULL non-null constraint---------------------------CREATE TABLE test4 (#建立非空约束id int not null,name varchar () Default ' ABCD ' Not NULL, #默认值就是nullage int null); #取消非空约束 ALTER TABLE TEST4 modify name varchar (in) The default ' ABCD ' NOT NULL, #增加非空约束 alter t Able TEST4 Modify Age int not null;#--------------------------------unique: Unique constraint--------------------------------# Column-level constraint syntax establishes constraint CREATE TABLE test_unique (#建立行级唯一约束 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 table-level constraint syntax to establish a unique constraint, specifying that test6_id and test6_name Two column combinations cannot repeat constraint test6_unique unique (test6_id,test6_name), #使用表级约束语法建立唯一约束, Constraint named Test6_unique_2,test6_pass cannot repeat constraint test6_unique_2 unique (test6_pass)); #add关键字增加唯一约束 ALTER TABLE TEST4Add unique (id,name,age); #modify关键字删除或者增加唯一约束 ALTER TABLE TEST4 modify age varchar (255) is not null; ALTER TABLE TEST4 modify age varchar (255) is not null unique; #对大部分数据库而言, delete constraint use: ALTER TABLE name DROP CONSTRAINT constraint name #但是Mysql不采取此方式, instead: ALTER TABLE name DROP INDEX constraint name #------------------ --------------PRIMARY key: PRIMARY KEY constraint--------------------------------#主键约束相当于非空约束和唯一约束. #每个表只允许拥有一个主键, but this primary key can consist of multiple columns of data that cannot be duplicated #标准SQL允许给主键自行命名, but for MySQL it does not have any effect, and it is always the default name primary CREATE table Primary _test (#使用列级语法建立主键约束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), # Specifies that the primary KEY constraint name is TEST2_PK, which is valid for most databases, but not valid for MySQL, the primary KEY constraint name is still primaryconstraint TEST2_PK primary key (test_id)); #以多列组合创立主键 CREATE TABLE Primary_test3 (test_id int,test_name varchar (255), primary key (Test_id,test_name)); #使用列级约束语法 ALTER TABLE PRIMARY_TEST3 modify TEST_ID int primary key (); #使用表级约束语法 ALTER TABLE PRIMARY_TEST3 add primary key (Test_id,test_NAME); #删除主键约束: ALTER TABLE name drop PRIMARY key; #主键列自增长特性: If the type of a data column is an integral type and the column is the primary key column, you can specify that the column has self-growth #mysql使用auto_increment来设置自增长, when you insert a record into the table, you do not specify a value for the column, and the system generates the CREATE table PRIMARY_TEST3 (//Establish PRIMARY KEY constraint, set self-growth test_id int auto_increment primary key,test_name varchar (255)); #外键约束 FOREIGN KEY #Mysql中只有表级语法建立的外键约束才可以生效 #为保证参照主表的存在, first create the Main Table CREATE TABLE TEACHER_TB (t_id int auto_increment,t_name var char (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) R Eferences TEACHER_TB (t_id)); #如果使用表级约束语法, you need to specify the foreign key column of this table using the foreign key, if you do not specify a constraint name when creating a foreign KEY constraint #则mysql会为该外键约束命名为table_name_ Ibfk_n, where table_name is the table name from the table, 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_t EACHER_FK foreign KEY (T_java) references Teacher_tb2 (t_id)); #建立多列组合外键约束 CREATE TABLE Teacher_tb5 (t_namevarchar (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_jav A_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; #增加外键约束 ALTER TABLE STUDENT_TB2 add foreign key (T_java) references teacher_tb2 (t_id); #外键约束参照自身, self-constraining 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)); #定义当删除主表记录时, delete the table record as well #on delete CASCADE all cascade deletes from table records referenced to the main table record #on delete set NULL sets the From table record referencing the main table record from the table to null e C reate 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_t EACHER_FK foreign KEY (T_java) references Teacher_tb8 (t_id) on DELETE cascade); 

What are the five integrity constraints--data constraint instances based on MySQL database