[打卡]约束-赵梓焓

#NOT NULL 非空约束 只能用列级约束，不能用表级约束

CREATE DATABASE dbtest;

USE dbtest;

#在 CREATE DATABASE 时添加约束

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','2911@qq.com',5000);

SELECT * FROM test1;

#错误
INSERT INTO test1
(id,last_name,email,salary)
VALUES (NULL,'sam','2911@qq.com',5000)

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

#在 ALTER TABLE 时删除约束

ALTER TABLE test1
MODIFY last_name VARCHAR(15) NULL;

DESC test1;

#UNIOQE 唯一性

#在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 时添加约束

#方式一

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','291@qq.com',6000);

#方式二

DESC test2;

ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_name = 'test2';