甄帅垚 -----约束

#not null 非空约束 只能用列级约束，不能用表级约束

create database dbtest;

use dbtest;

#3.1 在 CREATE TABLE 时添加约束

create table test1(
id int not null,
last_name varchar(25) not null,
email varchar(20),
salary int );

desc test1;

insert into test1
(id,last_name,email,salary)
values
(1,'tom',tom@126.com,5000);
# 3.2 在 ALTER TABLE时添加约束
select * from test1;

update test1
set email = null
where id=1;

update test1
set salary= null
where id =1;

#在 alter table时添加约束
# 本身有NULL值，再改为NOT NULL不能添加
alter table test1
modify email varhcar(25) not null ;

# 3.3 ALTER TABLE时删除约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;
DESC test1;

alter table TEST1
MODIFY LAST_NAME varchar(15) null;

# 4. UNIQUE 唯一性约束

# 4.1 在CREATE TABLE时添加约束

create table test2(
id int unique ,#列
last_name varchar(15),
email VARCHAR(25),
salary INT

constraint uk_test2_email unqiue(email)
);

desc test2;

#在alter table 时添加约束

#方式一

alter table test2
add constraint uk_test2_sal unique (salary);

desc test2 ;

select * from test2;

insert into test2
(id ,last_name, email,salary)
values
(1,'tom','2911@qq.com',5000);

insert into test2
(id ,last_name, email,salary)
values
(2,'sam','2911@qq.com',9000);

#方式二

desc test2;

alter table test2
modify last_name varchar(15) unique;

select * from information_schema.table_constratnts
where table_name='test2';