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;