Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of data constraints in the MySQL database, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

In order to prevent the non-conforming data from entering the database, when the user inserts, modifies and deletes the data, DBMS automatically monitors the data according to certain constraints, so that the data that does not conform to the specification can not enter the database, so as to ensure that the data stored in the database is correct, effective and compatible.

# data constraints

# five integrity constraints: # NOT NULL: a non-null constraint that specifies that a column cannot be empty # UNIQUE: unique constraint, specify that a column or combination of columns cannot be repeated # PRIMARY KEY: primary key, specify the value of this column to uniquely identify the column record # FOREIGN KEY: foreign key, specify that this record belongs to a record in the master table, mainly for referential integrity # CHECK: check, specify a Boolean expression Used to specify that the corresponding value must satisfy the expression (mysql does not support check constraint) #-NOT NULL non-empty constraint-create table test4 (# create non-empty constraint id int not null,name varchar (55) default 'ABCD' not null,# default value is nullage int null) # cancel non-empty constraint alter table test4 modify name varchar (55) default 'ABCD' not null,# and add non-empty constraint alter table test4 modify age int not null #-- UNIQUE: unique constraint-- # column-level constraint syntax create constraint create table test_unique (# create 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, test6_pass varchar), # use table-level constraint syntax to establish unique constraints, specify that the combination of test6_id and test6_name columns cannot repeat constraint test6_unique unique (test6_id,test6_name), # use table-level constraint syntax to establish unique constraints, and the constraint is called test6_unique_2 Test6_pass cannot repeat constraint test6_unique_2 unique (test6_pass)) # add keyword adds unique constraint alter table test4 add unique (id,name,age); # modify keyword deletes or adds 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 are used: alter table table name drop constraint constraint name # but Mysql does not do this Instead: alter table table name drop index constraint name #-PRIMARY KEY: primary key constraint-- # primary key constraint is equivalent to non-null and unique constraints. # only one primary key per table is allowed, but this primary key can consist of multiple data columns. 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. It is always called PRIMARY create table primary_test by default (# using column-level syntax to establish a primary key constraint test_id int primary key,test_name varchar (255)) # create a primary key constraint create table primary_test2 using table-level syntax (test_id int not null,test_name varchar, test_pass varchar, # specifies that the primary key constraint name is test2_pk, which is valid for most databases, but not for mysql, and the primary key constraint name is still PRIMARYconstraint test2_pk primary key (test_id)) # create primary key create table primary_test3 with multiple column combinations (test_id int,test_name varchar, 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 primary key constraint: alter table table name drop primary key # self-growing feature of primary key column: if the type of a data column is integer and the column is used as a primary key column, you can specify that the column has the function of self-growing. # mysql uses auto_increment to set self-growing, but does not specify a value for the column when inserting records into the table, and the system generates create table primary_test3 (/ / establish primary key constraints, set self-growing test_id int auto_increment primary key,test_name varchar (255)) # Foreign key constraints in FOREIGN KEY # Mysql only foreign key constraints established by table-level syntax can take effect # to ensure the existence of reference to the main table, first establish the main 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 create a foreign key constraint without specifying a constraint name, # mysql will name the foreign key constraint table_name_ibfk_n, where table_name is the table name from the table, and n is the integer create table teacher_tb2 (t_id int auto_increment,t_name varchar (255), primary key (t_id)) starting from 1. 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)); # Establishment of multi-column combination foreign key constraints create table teacher_tb5 (t_name varchar (255), t_pass varchar (255), primary key) Create table student_tb5 (s_id int auto_increment primary key,s_name varchar) not null,t_java_pass varchar (255), t_java_name varchar (255), foreign key (tonal java t_java constraint) references teacher_tb5); # 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 constraints refer to themselves, 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)) # define that when the master table record is deleted, the slave table record is deleted accordingly # on delete cascade deletes all the slave table records referencing the master table record in cascade # on delete set null sets the slave table record referencing the master table record 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) Thank you for reading this article carefully. I hope the article "sample Analysis of data constraints in MySQL Database" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.