P67 非空约束的使用

2.非空约束

#2. 如何查看表中的约束
SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test1';

CREATE DATABASE dbtest13;
USE dbtest13;
#3.not null 非空约束
#3.1 在create table时添加约束

CREATE TABLE test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);

DESC test1;

INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',3400);

#错误：Column 'last_name' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(2,NULL,'tom@126.com',3400);
#错误：Column 'id' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(NULL,'Jerry','tom@126.com',3400);
#错误：name没有赋值，默认为null，所以报错
INSERT INTO test1(id,email)
VALUES(2,'abc@126.com');

#错误的：不能把name设成null Column 'last_name' cannot be null
UPDATE test1
SET last_name=NULL
WHERE id=1;

#3.2 在alter table时添加约束
DESC test1;

#如果当前表里 email处有一个null值都会报错
ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;

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