[打卡]约束——bckpf

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

CREATE DATABASE dbtest;

USE dbtest;

#在CREATE TABLE 时添加约束

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

DESC test1;

INSERT INTO test1
(id,last_name,email,salary)
VALUES
(1,'dog','tom@163.com',5000);

SELECT * FROM test1;

INSERT INTO test1
(id,last_name,email,salary)
VALUES
(NULL,'Sam','tom@163.com',6000);

UPDATE tset1
SET email = NULL
WHERE id=1;

UPDATE tset1
SET salary = 6000
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(10) NULL;

DESC test1;

#UNIQUE 唯一性约束

#在CREATE TABLE 时添加约束

CREATE TABLE test2(
id INT UNIQUE, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary INT,
#表级约束

CONSTRAINT uk_tset2_email UNIQUE(email)
);

DESC test2;

INSERT INTO test2
(id,last_name,email,salary)
VALUES
(1,'tom','tom@163.com',8000);

SELECT * FROM test2;

#错
INSERT INTO test2
(id,last_name,email,salary)
VALUES
(1,'Sam','tom@163.com',7000);

INSERT INTO test2
(id,last_name,email,salary)
VALUES
(2,'Sam','tom@163.com',7000);

#在ALTER TABLE时添加约束

#方式1
DESC test2;

ALTER TABLE test2
ADD CONCURRENT uk_test2_sal UNIQUE(salary);

#方式2
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

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