文章目录
1 数据的完整性作用
- 保证保存到数据库中的数据是正确的
- 它可以防止可能的输入错误
- 确保数据的完整性 = 在建表的时候给表添加约束
2 完整性的分类
实体完整性 域完整性 引用完整性
2.1 实体完整性
- 实体：表中的一行数据代表一个实体
- 实体完整性的作用：保证每一行数据不重复
通过实体完整性约束来实现
- 实体完整性的约束类型：
主键约束 primary key 数据唯一，而且不能为空
唯一约束 unique 数据唯一
自动增长列 auto_increment
2.1.1 主键约束 primary key
- 特点：数据唯一，而且不能为空
- 添加方式一：
CREATE TABLE stu(
id INT PRIMARY KEY,
NAME VARCHAR(10)
);
INSERT INTO stu VALUES(2,'aaa');
SELECT * FROM stu;
- 添加方式二：可以建立联合主键
CREATE TABLE stu2(
id INT,
NAME VARCHAR(10),
PRIMARY KEY(id)
);
INSERT INTO stu2 VALUES(1,'aaa');
SELECT * FROM stu2;
CREATE TABLE stu3(
id INT,
NAME VARCHAR(10),
classid INT,
PRIMARY KEY(id,classid)
);
INSERT INTO stu3 VALUES(2,'aaa',1);
SELECT * FROM stu3;
- 添加方式三：
CREATE TABLE stu4(
id INT,
NAME VARCHAR(10)
);
ALTER TABLE stu4 ADD CONSTRAINT PRIMARY KEY(id);
ALTER TABLE stu4 ADD PRIMARY KEY(id);
ALTER TABLE stu4 ADD CONSTRAINT pk_stu4_id PRIMARY KEY(id);
- 删除主键：
ALTER TABLE stu4 DROP PRIMARY KEY;
2.1.2 唯一约束 unique
- 特点：数据唯一
- 添加方式：
CREATE TABLE stu5(
id INT PRIMARY KEY,
NAME VARCHAR(10) UNIQUE
);
INSERT INTO stu5 VALUES(4,NULL);
SELECT * FROM stu5;
2.1.3 自动增长列 auto_increment
- 给主键列添加的一个自动增长的数值，列只能是整数类型
- 如果删除之前自动增长的数值，后面再次添加的时候不会再次出现被删除的值，而是接着别删除之后的值开始
CREATE TABLE stu6(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
);
INSERT INTO stu6(NAME) VALUES ('aaa');
SELECT * FROM stu6;
INSERT INTO stu6 VALUE(NULL,'bbb');
INSERT INTO stu6 VALUE(20,'ccc');
DELETE FROM stu6 WHERE id=22;
2.2 域完整性
- 表示表中的列必须符合某种特点的数据类型或者约束类型。
- 域：表示列（表示单元格）
- 作用：保证此列下的每一个单元格的数据是正确的。但是不对照此列的其他单元格进行比较
- 域完整的约束：
数据类型 ：数值，日期，字符串。。。
非空约束 not null
默认值约束 default
检查约束 CHECK
2.2.1 非空约束 not null
CREATE TABLE stu7(
id INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL
);
INSERT INTO stu7 VALUES(2,'aaa');
SELECT * FROM stu7;
2.2.2 默认值约束 default
CREATE TABLE stu8(
id INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
gender VARCHAR(10) DEFAULT '男'
);
INSERT INTO stu8(id,NAME) VALUES(2,'aaa');
SELECT * FROM stu8;
INSERT INTO stu8 VALUES(3,'aaa','女');
INSERT INTO stu8 VALUES(4,'aaa',NULL);
INSERT INTO stu8 VALUES(5,'aaa',DEFAULT);
2.2.3 检查约束 CHECK 8.0.15之前版本不支持
CREATE TABLE stu9(
id INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
gender VARCHAR(10) CHECK(gender='男' OR gender='女')
);
INSERT INTO stu9 VALUES(1,'aaa','人妖');
SELECT * FROM stu9;
DELETE FROM stu9 WHERE id=1;
2.3 引用完整性（参照完整性）
- 外键约束 foreign key
- 第一种添加方式：
CREATE TABLE emp1(
id INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
deptid INT,
CONSTRAINT fk_dept_emp1_depid FOREIGN KEY(deptid) REFERENCES dept(deptid)
);
- 第二种添加方式：
ALTER TABLE emp ADD CONSTRAINT fk_deptid FOREIGN KEY(deptid) REFERENCES dept(deptid);
- 删除外键约束：
-- alter table 表名 drop foreign key 外键约束名;
ALTER TABLE emp DROP FOREIGN KEY fk_deptid;
-
注意事项：
要有外键，必须现有主键。
要在子表种添加外键约束，主表中引用的那一列必须是主键
主键和外键的类型要一致。
保证一个表的外键和另一个表的主键对应。 -
引用完整性究竟是什么？
指的是子表外键的值引用主表中主键的值
一旦有了约束，子表中外键的所有值都必须是主表中主键有的值。
3 小结
- 实体完整性描述的行
- 域完整性描述的是列
- 引用完整性描述的是关系
4 引用完整性描述的是表和表之间的关系，
4.1 关系分类：
-
一对一：主表中的一条数据对应子表中的一条数据
-
一对多(多对一)：主表中的一条数据对应子表中的多条数据
-
多对多：主表中的多条数据对应子表中的多条数据
-
举例说明：
一对一：身份证号 学校的学号 国家的一夫一妻制…
一般一对一就放到一张表中。
如果非要用2张表来表示，怎么办？ 人和身份证号
需要先找出主从关系。
人可以没有身份证，但是有身份证就必然对应一个人
所以人是主表person，身份证是子表card。
4.2 方案
如何设计？2种方案：
4.2.1 在card种添加外键，并给外键添加唯一约束
CREATE TABLE person(
id INT PRIMARY KEY,
NAME VARCHAR(10)
);
CREATE TABLE card(
cid INT PRIMARY KEY,
id INT UNIQUE,
CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES person(id)
);
4.2.2 给card中的主键添加外键约束
CREATE TABLE card2(
cid INT PRIMARY KEY,
CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES person(id)
);
一对多：dept和emp 一个爸爸有多个孩子 我有多个手机，玩具，电脑。。。
至少要用2张表来描述。
都是在多方创建外键
多对多：老师和学生
至少需要3张表，需要创建中间表。在中间表中给出2个列
一个引用老师表，一个引用学生表。添加2个外键约束，保证引用关系正确。
CREATE TABLE student(
sid INT PRIMARY KEY,
sname VARCHAR(10) NOT NULL
);
CREATE TABLE teacher(
tid INT PRIMARY KEY,
tNAME VARCHAR(10) NOT NULL
);
CREATE TABLE stu_tea(
sid INT,
tid INT
);
ALTER TABLE stu_tea ADD CONSTRAINT fk_tea_tid FOREIGN KEY(tid) REFERENCES teacher(tid);
ALTER TABLE stu_tea ADD CONSTRAINT fk_stu_sid FOREIGN KEY(sid) REFERENCES student(sid);