NNNNNNNNN

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

CREATE DATABASE dbtest;
use dbtest;

#在create TABLE 时添加约束
CREATE TABLE test1(

id INT NOT NULL,

last_name VARCHAR(10) NOT NULL,

email VARCHAR(25),

salary INT
);

DESC test1;

INSERT INTO test1
(id,last_name,email,salary)
VALUES 
(1,'nn','314@163.com',99);

SELECT * FROM test1;

INSERT INTO test1
(id,last_name,email,salary)
VALUES 
(2,NULL,'314@163.com',99);

INSERT INTO test1
(id,email)
VALUES
(4,'abc@163.com');

DESC test1;

UPDATE test1
SET email = NULL
WHERE id = 4;

#在 ALTER TABLE 时添加约束

SELECT * FROM test1;

DESC test1;

ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;

# ALTER TABLE 删除约束

ALTER TABLE test1
MODIFY email VARCHAR(20) NULL;

# UNIQUE 唯一性约束 
#在 CREATE TABLE时添加约束
CREATE TABLE test2(
id INT UNIQUE,
last_name VARCHAR(15),
email VARCHAR(25),
salary INT
);

DESC test2;

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

#表级约束
CONSTRAINT uk_test3_email UNIQUE(email)
);
DESC test3;

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

SELECT * FROM test3;


INSERT INTO test3
(id,last_name,email,salary)
VALUES(2,'sam','ll@163.com',8000);

#在 ALTER TABLE时添加约束
#方式一
UPDATE test3
SET salary = 3000
WHERE id = 1;

DESC test3;

ALTER TABLE test3
ADD CONSTRAINT uk_test3_sal UNIQUE(salary);

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

DESC test3;