[打卡]约束123

#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','123@163.com',6666);

SELECT * FROM test1;

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

UPDATE test1
SET email = NULL
WHERE id = 1;

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

DESC test1;

#UNIQUE 唯一性约束

CREATE TABLE test2(

id INT UNIQUE,#列级约束
last_name VARCHAR(15),
email VARCHAR(25),
salary INT,
#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)

);

DESC test2;

1 tom tom@163.com 8000

SELECT * FROM test2;

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

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

#在ALTER TABLE 时添加约束

#方式1
DESC test2;

ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

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

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