数据完整性和约束的分类

一、约束的概述

1.为什么需要约束
数据完整性是指数据的精确性和可靠性。它是防止数据库 中存在不符合 语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
①实体完整性：例如，同一个表中，不能存在两条完全相同无法区分的记录。
②域完整性：例如，年龄范围0-120，性别范围“男/女”
③引用完整性：例如，员工在部门，在部门表中要能找到这个部门
④用户自定义完整性：例如，用户名唯一，密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

1.3约束的分类：
角度1：约束的字段的个数
单列约束 VS 多列约束

角度2：约束的作用范围
列级约束：将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束

角度3：约束的作用（功能）
① not null （非空约束）
②unique (唯一性约束)
③ primary key（主键约束）
④foreign KEY (外键约束)
⑤check （检查约束）
⑥default(默认值约束)

如何添加/删除约束？
CREATE TABLE 时添加约束
ALTER TABLE 时增加约束，删除约束

二、约束的分类

not null （非空约束）

特点：

#数据完整性和约束的分类
/*
什么叫约束？对表中字段的限制

约束的分类：
角度1：约束的字段的个数
单列约束 VS  多列约束

角度2：约束的作用范围
列级约束：将此约束声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束

角度3：约束的作用（功能）
① not null （非空约束）
②unique (唯一性约束)
③ primary key（主键约束）
④foreign KEY (外键约束)
⑤check （检查约束）
⑥default(默认值约束)

如何添加/删除约束？
CREATE TABLE 时添加约束
ALTER TABLE 时增加约束，删除约束
*/

#如何查看表中的约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'employees';

CREATE DATABASE dbtest13;
USE dbtest13;
#not null （非空约束）
#在CREATE TABLE 时添加约束
CREATE TABLE  test1(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);

DESC test1;

INSERT INTO test1(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com','3400');

#错误： Column 'last_name' cannot be null
INSERT INTO test1(id,last_name,email,salary)
VALUES(2,NULL,'tom@126.com','3400');

UPDATE test1
SET last_name = NULL
WHERE id = 1;
#在ALTER TABLE 时增加约束
DESC test1;

ALTER TABLE test1
MODIFY email VARCHAR(25) NOT NULL;
#Column 'last_name' cannot be null
UPDATE test1
SET last_name = NULL
WHERE id = 1;

SELECT * FROM test1;

#在ALTER TABLE 时删除约束
ALTER TABLE test1
MODIFY email VARCHAR(25) NULL;

unique (唯一性约束)

#在CREATE TABLE 时添加约束

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

#表级约束
CONSTRAINT uk_test2_email UNIQUE(email)
);

DESC test2;

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

#在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同

INSERT INTO test2(id,last_name,email,salary)
VALUES (1,'Tom','tom@126.com',4500);

# Duplicate entry '1' for key 'test2.id'
INSERT INTO test2(id,last_name,email,salary)
VALUES (1,'Tom1','tom1@126.com',4600);

#可以向声明为unique的字段上添加null值。
INSERT INTO test2(id,last_name,email,salary)
VALUES (3,'Tom1',NULL,4600);

SELECT *FROM test2;

#在ALTER TABLE 时添加约束
DESC test2;

UPDATE test2
SET salary = 5000
WHERE id=3;

ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

ALTER TABLE test2
MODIFY last_name VARCHAR(15)UNIQUE;

#复合的唯一性约束
CREATE TABLE USER(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(15),

#表级约束
CONSTRAINT uk_user_name_pwd UNIQUE(`name`,`password`)
);

INSERT INTO USER
VALUES (1,'Tom','abc');
#可以添加成功
INSERT INTO USER
VALUES (1,'Tom1','abc');

SELECT * from USER;
 
#删除唯一性约束
/*
--添加唯一性约束的列上也会主动创建唯一索引
--删除唯一约束只能通过删除唯一索引的方式删除
--删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样
--如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列那么默认和列名相同，那么默认和（）中排在第一个的列名相同。也可以自定义唯一性约束
*/

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

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

#如何删除唯一性索引

ALTER TABLE test2
DROP INDEX last_name;

primary key（主键约束）

特点：主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

#建表时指定主键约束（create table）
create TABLE test4(
id INT PRIMARY KEY,#列级约束
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
#MySQL的主键名总是PRIMARY，就算自己命名了主键约束也没用
create TABLE test5(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25),
#表级约束
CONSTRAINT pk_test5_id PRIMARY KEY(id) #没有必要起名字
);

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

INSERT INTO test4(id,last_name,salary,email)
VALUES(1,'Tom',4500,'tom@126.com');

SELECT * FROM test4;


CREATE TABLE user1(
id INT,
`name` VARCHAR(15),
`password` VARCHAR(25),
PRIMARY KEY (name,password)
);

INSERT INTO user1
VALUES (1,'Tom','abc');

#Column 'name' cannot be null
INSERT INTO user1
VALUES (1,NULL,'abc')

SELECT * FROM user1;

#在ALTER TABLE 时添加约束

create TABLE test6(
id INT ,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)
);
DESC test6;

ALTER TABLE test6
ADD PRIMARY KEY (id);

#如何删除主键约束(在实际开发中，不会主动删除主键约束)

ALTER TABLE test6
DROP PRIMARY KEY;

自增列：AUTO_INCREMENT
特点：某个字段的值自增。

foreign KEY (外键约束)

FOREIGN KEY 约束
作用：限定某个表的某个字段的引用完整性。

#FOREIGN KEY 约束
#在create table 时添加

#主表和从表，夫表和子表

#先创建主表
USE dbtest13;

CREATE TABLE dept1(
dept_id INT,
dept_name VARCHAR(15)
);
#创建从表
CREATE TABLE emp1(
emp_id INT PRIMARY KEY auto_increment,
emp_name VARCHAR(15),
department_id INT,
#表级约束
CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1 (dept_id)
);
#上述操作报错：因为主表中的dept_id没有主键约束或唯一性约束。

ALTER TABLE dept1
ADD PRIMARY KEY (dept_id);
DESC  emp1;

#演示外键效果

INSERT INTO dept1
VALUES (10,'IT');

INSERT INTO emp1
VALUES(1001,'Tom',10);
#删除失败
DELETE FROM dept1
WHERE dept_id = 10;
#更新失败
UPDATE dept1
SET dept_id = 20
WHERE dept_id = 10;

#在ALTER TABLE 时添加外键约束
CREATE TABLE dept2(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(15)
);

CREATE TABLE emp2(
emp_id INT PRIMARY KEY auto_increment,
emp_name VARCHAR(15),
department_id INT
);

DESC emp2;

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY (department_id) REFERENCES dept2(dept_id);

#删除外键约束
#一个表中可以声明多个外键约束
USE atguigudb;
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'employees';

USE dbtest13;
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'emp1';

ALTER TABLE emp1
DROP FOREIGN KEY fk_emp1_dept_id;

#再手动删除外键约束对应的普通约束
SHOW INDEX FROM emp1;

ALTER TABLE emp1
DROP INDEX fk_emp1_dept_id;

check （检查约束）

作用：检查某个字段的值是否符合XX要求，一般指的是值的范围

#MySQL5.7不支持check约束，但8.0支持
USE dbtest13;

CREATE TABLE test10(
id  INT,
last_name VARCHAR(15),
salary DECIMAL(10,2) CHECK (salary > 2000)
);

INSERT INTO test10
VALUES(1,'Tom',2500);

SELECT* FROM test10;

default(默认值约束)

作用：给某个字段/列指定默认值，一旦设置默认值。在插入数据时，如果此字段没有显示赋值，则赋值为默认值

#在 create table 时添加约束
CREATE TABLE test11(
id INT,
last_name VARCHAR(15),
salary DECIMAL (10,2) DEFAULT 2000
);

DESC test11;

INSERT INTO test11(id,last_name,salary)
VALUES(1,'Tom',3000);

INSERT INTO test11(id,last_name)
VALUES(2,'Tom1');

SELECT * FROM test11;

#在alter table 时添加约束

CREATE TABLE test12(
id INT,
last_name VARCHAR(15),
salary DECIMAL (10,2) 
);

DESC test12;

ALTER TABLE test12
MODIFY salary DECIMAL(8,2) DEFAULT 2500;

#在alter table 删除约束
ALTER TABLE test12
MODIFY salary DECIMAL(8,2);