[打卡]张士晨 约束

# not null（非空约束）只能是列级数列
CREATE database dbtest;

use dbtest ;

#在create table时添加约束

CREATE table test1(
id int not null, last_name VARCHAR(15) not null,
email VARCHAR(20),
salary INT

);

desc test1;

INSERT into test1
(id,last_name,email,salary)
VALUES(1,'TOM','tom@126.com',500)

SELECT*from test1;

INSERT into test1
(id,last_name,email,salary)
VALUES(1,'Sam','Sam@126.com',9000)

UPDATE test1
set email=null
where id=1;

UPDATE test1
set salary=null
where id=1;

desc test1;

#在alter table时添加约束
#如果本身有null，再改为not null的话不能再添加
alter table teat1
MODIFY email VARCHAR(25) not null;
#再alter table 时删除约束

alter table tese1
modify last_name VARCHAR(15)null;

desc test1;
#unique 唯一性约束
#create table时添加约束

CREATE table test2(

id int UNIQUE,#列级约束
last_name VARCHAR(15),

email VARCHAR(25),


salary int,
#表级约束

constraint uk_test2_email unique(email)
);
desc test2;
#在alter table 时添加约束
#方式1
ALTER table test2
add CONSTRAINT uk_test2_sal UNIQUE(salary);
desc test2;
ALTER table teat2
modify last_name VARCHAR(15) UNIQUE;

SELECT *from information_schema.TABLE_CONSTRAINTS
where table_name='test2';