MySQL integrity constraints definition and example tutorial

Method 1: Add a primary key constraint while creating a table.
create table student(
    sid int primary key,
    sname varchar(20),
    age int
)charset=utf8;
-- After adding the primary key constraint, inserting the same sid will result in an error.
insert into student(sid,sname,age) values ​​(1,'张三',22);
insert into student(sid,sname,age) values ​​(1,'李四',33);

Method 2: Add a primary key constraint while creating the table, but the adding method is different.
  "Only this method of adding a primary key can add a composite primary key"
create table student(
    sid int,
    sname varchar(20),
    age int,
    primary key(sid)
)charset=utf8;

Method 3: After creating the table, add a primary key constraint.
create table student(
    sid int,
    sname varchar(20),
    age int
)charset=utf8;                  
alter table student add primary key(sid);
-- constraint means that a constraint is added;      
-- pk_sid is a name for the constraint;

"Single primary key"
    Set a field in the table as the primary key and use this field to uniquely identify a record.
    As long as sid is unique and not empty, each row is a unique record.
    primary key(sid)
    sid sname age
    1 Zhang San18
    2 Zhang San18  

"Joint Primary Key"
    Set two fields (or multiple fields) in the table as joint primary keys, using these two fields (or multiple fields),
    To uniquely identify a record.
    These two fields can be repeated separately, as long as they are not repeated at the same time.
    primary key (sid, classid)
    sid classid sname age
    1 2 Zhang San18
    1 3 Zhang San18  
    2 4 Li Si20
    3 4 Wang Wu 30
    -- The following two records have the same fields, which indicates a duplicate record and an error occurs when inserting.
    5 5 Li Chuang25  
    5 5 Wang Er24

alter table student drop primary key;

Method 1: When creating a table, add the primary key and the primary key auto-increment at the same time. 
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;  
-- Use primary key and auto_increment together when inserting data.
-- There is no need to assign a value to the primary key field sid.
insert into student(sname,age) values ​​("张三",22),("李四",25);

Method 2: After creating the table, add "primary key and primary key auto-increment" to the field at the same time.
create table student(
   sid int,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;  
-- The following two methods can both add "primary key and primary key auto-increment" to a field at the same time.
alter table student modify sid int primary key auto_increment;
alter table student change sid sid int primary key auto_increment;

alter table student modify sid int primary key;

Method 1: Add a unique constraint while creating the table.
create table student(
    sid int primary key key auto_increment,
    sname varchar(20),
   age int,
   idcard varchar(18) unique
)charset=utf8; 


Method 2: Add a unique constraint while creating the table, but in a different way.
  "Only this method of adding a primary key can add a composite primary key"
create table student (
    sid int primary key auto_increment,
    sname varchar(20),
    age int,
    idcard varchar(18),
    unique(idcard)
)charset=utf8; 

Method 3: After creating the table, add a unique constraint.
create table student (
    sid int primary key auto_increment,
    sname varchar(20),
    age int,
    idcard varchar(18)
)charset=utf8;                      
alter table student add unique(idcard);

-- For a field with a unique constraint set, an error will be reported when the same value is inserted.
insert into student(sname,age,idcard) values ​​("李四",18,"123456");
insert into student(sname,age,idcard) values ​​("王五",22,"123456");

To delete a unique constraint, the statement used is slightly different.
alter table student drop key idcard;

Method 1: When creating a table, add a not null constraint to the field. 
create table student(
   sid int primary key auto_increment,
   sname varchar(20) not null,
   age int,
   idcard varchar(18) 
)charset=utf8;

Method 2: After creating the table, add a not null constraint to the field.
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18) 
)charset=utf8;

-- There are two ways to add.
alter table student modify sname varchar(20) not null;
alter table student change sname sname varchar(20) not null;

"Sometimes we don't notice these details, so we explain them separately."
1: After the field is set with a non-null constraint, you cannot insert a null value.
-- The following insertion will result in an error.
insert into student(sname,age) values ​​(null,22);

2: After the non-null constraint is set for a field, not only can a null value not be inserted, but also no value can be inserted.
-- The following insertion will also report an error.
insert into student(age,idcard) values ​​(33,"123456789");

Method 1: When creating a table, add a default value to the field. 
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int default 0,
   idcard varchar(18)
)charset=utf8;

Method 2: After creating the table, add default values ​​to the fields.
create table student(
   sid int primary key auto_increment,
   sname varchar(20),
   age int,
   idcard varchar(18)
)charset=utf8;
"Different points need special attention"
-- The code to add a default value using alter is a bit special.
alter table student alter age set default 0;

alter table student alter column age drop default;

Method 1: When creating a table, add a foreign key constraint to the field. 
create table classroom(
    cid int primary key auto_increment,
    cname varchar(20) not null
)charset=utf8;

create table student(
   sid int primary key auto_increment,
   sname varchar(20) not null,
   age bit(1),
   cid int,
   constraint fk_cid foreign key(cid) references classroom(cid)
)charset=utf8;

Method 2: After creating the table, add a foreign key constraint to the field.
create table classroom(
    cid int primary key,
    cname varchar(20) not null
)charset=utf8;

create table student(
    sid int primary key auto_increment,
    sname varchar(20) not null,
    age bit(1),
    cid int
)charset=utf8;

alter table student add constraint fk_cid foreign key(cid)
references classroom(cid);
"Note: constraint is followed by an alias, and fk_cid is equivalent to the alias."