[打卡]郭佳豪-添加约束

# 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',5000)

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 VARCHAR(25) NOT NULL;

# 在ALTER TABLE 时删除约束

ALTER TABLE test1
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);

-----------------------
SELECT * FROM test2;

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

# 方式2

DESC test2;

ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

SELECT * FROM information_schema.table_constraints
WHERE table_name = 'test2';