[打卡]张博研——复习.约束

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

SELECT * FROM test1;

INSERT into test1(id,last_name,email,salary)
VALUES
(NULL,'Sam','Sam@126.com',9000)

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);

#UNIQUE 唯一性约束

#在CREATE TABEL 时添加约束

CREATE TABLE test2(

id INT UNIQUE,

last_name VARCHAR(25),

email VARCHAR(25),

salary int,

CONSTRAINT uk_test2_eamil UNIQUE(email)

);

DESC test2;

#在 ALTER TABLE 时添加约束

#方式1

ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE (salary);

DESC test2;

INSERT into test2(id,last_name,email,salary)
VALUES
(2,'Sam','Sam@126.com',9000)

INSERT into test2(id,last_name,email,salary)
VALUES
(1,'Tom','tom@126.com',500)

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