数据库进阶学习之数据完整性(约束)

数据完整性 约束

为了保证在操作的时候数据库中的数据能够正确反应现实情况,在表中会对数据做一些约束

• 实体完整性 一条学生信息必须包含学号(不可缺少)
• 域完整性 名字 123 类型限制 取值范围限制 年龄0~150
• 引用完整性 不能引用不存在的引用 不能插入一个不存在班级的同学
• 自定义完整性 自己的规则 自己灵活运用

非空约束 not null

值 不能为null 所有字段默认值都是null 所以设置not null必须赋值

创建表的时候添加非空约束

CREATE TABLE class(
classNo INT NOT NULL,
className VARCHAR(20)
);

添加或者删除not null约束

删除约束 去掉not null

ALTER TABLE class MODIFY COLUMN classNo INT;

加上约束 加上not null

ALTER TABLE class MODIFY COLUMN classNo INT NOT NULL;

注意：如果表中已经有null 设置非null属性会出问题 建议 先删除或者先修改表中非空字段

唯一性约束 unique

值只能在这个表中出现一次 可以为null 但是必须表中不能重复

在创建时添加唯一性约束

CREATE TABLE class(
classNo INT UNIQUE,
className VARCHAR(20)
);

创建后添加或删除唯一性约束

#删除 
ALTER TABLE class MODIFY COLUMN classNo INT;
#添加 
ALTER TABLE class MODIFY COLUMN classNo INT unique;

多列唯一性约束(组合约束)

用于多个字段组合唯一 比如说 用户名-id唯一 用户名部分重复或者id部分重复都可以 summer-233 summer-123 seven-233 可以同时存在

CREATE TABLE custom(
NAME VARCHAR(20) NOT NULL, #自己取的用户名
id INT NOT NULL,
passW VARCHAR(20) NOT NULL,
constraint name_id     #给约束取别名 方便后面的删除
UNIQUE(NAME,id) #多列唯一性约束
);

删除多列唯一性约束 alter table 表名 drop index 约束名;

添加多列约束 alter table 表明 add constraint 别名 unique(字段1,字段2,…);

注意：对于这里的别名部分都可以省略 如果省略的话 会默认生成一个
查看表的约束名 可以使用 show create table 表名; 查看
constraint name_id 后面的name_id是别名 constraint 是取别名用的

主键约束(primary key)

唯一且不能为NULL

单列主键约束

CREATE TABLE admin(
id INT PRIMARY KEY,
NAME VARCHAR(10)
);

添加或删除主键约束

ALTER TABLE admin DROP PRIMARY KEY; 删除表中的主键
ALTER TABLE admin ADD PRIMARY KEY(id); 添加一个主键

复合主键

如果是复合主键 两个属性都不可以为NULL,组合不能重复 但是单个可以重复

CREATE TABLE admin(
id INT,
NAME VARCHAR(10),
CONSTRAINT id_name PRIMARY KEY(id,NAME)
);

添加或删除复合主键

#添加复合主键
ALTER TABLE admin ADD PRIMARY KEY(id,NAME);
#删除复合主键
ALTER TABLE custom DROP PRIMARY KEY; 

自增约束(auto_increment)

自动增长 添加的时候 如果没有赋值 那么自动赋值，放到整数的后面

there can be only one auto column and it must be defined as a key

注意：只能有一个自动增长，并且必须为主键

DROP TABLE admin;
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
)AUTO_INCREMENT=8;	#从多少开始自动增长(可以不加这句)

INSERT INTO admin(NAME)
VALUES('张三'),('李四'),('王五');

添加或者删除自动增长的约束

#添加
ALTER TABLE admin MODIFY COLUMN id INT AUTO_INCREMENT;
#删除
ALTER TABLE admin MODIFY COLUMN id INT AUTO_INCREMENT;

限制值约束(check)

限制值的范围 mysql不支持 语法上没问题 但是不起作用

CREATE TABLE test(
age INT CHECK(age>0 AND age<100)
);

INSERT INTO test
VALUES(1),(2),(3),(-1),(101);
SELECT * FROM test;

对于mysql不支持check的解决方法:
a. 枚举 enum 列出所有值 范围不适合用枚举

CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
sex ENUM('男','女') DEFAULT '男'
);
INSERT INTO teacher(sex)
VALUES('男'),('女');
SELECT * FROM teacher;

SHOW CREATE TABLE teacher;

b. 使用触发器  ...后面讲
c. C++连接数据库  判断操作写到C++代码中

默认值约束(default)

设定指定字段的默认值(所有字段的初始默认值都为NULL)

ALTER TABLE teacher 
MODIFY COLUMN sex ENUM('男','女') DEFAULT '男';

删除默认值就是修改默认值为NULL
添加默认值就是修改默认值为指定的值,所以都是modify

外键约束 foreign key

用于两个表中的约束
班级(班号,班级名) 和学生表(学生id,学生名,班号)
学生表 班号参考 班级表的班号
班号存在于班级表中 才能插入,否则不可以插入

学生表字段引用班级表中的字段 子表字段引用父表字段

CREATE TABLE class(	# 父表
classNo INT PRIMARY KEY,
className VARCHAR(20) NOT NULL
);
CREATE TABLE stu(	# 子表
 id INT PRIMARY KEY,
 NAME VARCHAR(20) NOT NULL,
 cNo INT,  #班号  参考class中的classNo
 CONSTRAINT c_id	#给外键取别名 注意 外键别名 整个库中唯一的
 FOREIGN KEY(cNo) REFERENCES class(classNo)
);

INSERT INTO class (`classNo`,`className`) VALUE (1,'通信一班');
SELECT * FROM class;
INSERT INTO stu (`id`,`NAME`,`cNo`) VALUE (1001,'1001',1);
SELECT * FROM stu;

注意：

• 没有删除父表的权利
• 父表存在才能添加子表

添加或删除外键

#根据外键名字删除外键
ALTER TABLE stu DROP FOREIGN KEY c_id; 
#添加外键
ALTER TABLE stu ADD FOREIGN KEY(cNo) REFERENCES class(classNo);

对于级联更新/删除

对于外键后面的两句

ON UPDATE CASCADE
ON DELETE CASCADE

分别设置了在更新/删除父表时候子表的数据操作方式
on update设置更新时候的操作,

on delete 设置删除时候的操作

操作分类

1. cascade 删除父表字段,子表相关字段同步操作

2. no action 不允许删除/修改有学生的班级

   默认是no action

3. set null 删除班级,对应班级学生班号设置为null

   此方式前提是 子表对应字段不可以为 not null