05-约束

1.1 实体完整性约束

表中的一列数据代表一个实体（entity），实体完整性的作用即是标识每一列数据不重复、实体唯一。

1.1.1 主键约束

关键字：PRIMARY KEY 唯一，标识表中的一列数据，此列的值不可重复，且不能为 NULL。

# 为表中适用主键的列添加主键约束
CREATE TABLE Student(
    stuId INT PRIMARY KEY,  # 标识学生编号是唯一的，不能为null
    stuName VARCHAR(20),
    phone VARCHAR(11)
)CHARSET = utf8;

INSERT INTO Student(stuId, stuName, phone) VALUES(1, '小明', '18988889999');
INSERT INTO Student(stuId, stuName, phone) VALUES(1, '小红', '13833338888'); # error：主键 id 唯一

1.1.2 唯一约束

关键字：UNIQUE 唯一，标识表中的一列数据，不可重复，可以为 NULL(NULL可以重复)。

# 为表中列值不允许重复的列添加唯一约束
CREATE TABLE Student(
    stuId INT PRIMARY KEY,  # 标识学生编号是唯一的，不能为null
    stuName VARCHAR(20),
    phone VARCHAR(11) UNIQUE  # 标识手机号是唯一的，可以为null（为null时，null可重复）
)CHARSET = utf8;

INSERT INTO Student(stuId, stuName, phone) VALUES(1, '小明', '18988889999');
INSERT INTO Student(stuId, stuName, phone) VALUES(1, '小红', '13833338888'); # error：id 唯一
INSERT INTO Student(stuId, stuName, phone) VALUES(2, '小王', NULL);
INSERT INTO Student(stuId, stuName, phone) VALUES(3, '小张', NULL);

1.1.3 自动增长列

关键字：AUTO_INCREMENT 给主键数值列添加自动增长。从 1 开始，每次加 1；不能单独使用，和主键配合。

# 为表中主键列添加自动增长，避免忘记主键 ID 序号
CREATE TABLE Student(
    stuId INT PRIMARY KEY AUTO_INCREMENT,  # 搭配主键，标识自动增长，从 1 开始每次自增 1
    stuName VARCHAR(20),
    phone VARCHAR(11) UNIQUE
)CHARSET = utf8;

INSERT INTO Student(stuName, phone) VALUES('小明', '18988889999'); # stuId 1
INSERT INTO Student(stuName, phone) VALUES('小红', '13833338888'); # stuId 2
INSERT INTO Student(stuName, phone) VALUES('小王', NULL); # stuId 3
INSERT INTO Student(stuName, phone) VALUES('小张', NULL); # stuId 4

1.2 域完整性约束

限制列的单元格的数据正确性。

1.2.1 非空约束

关键字：NOT NULL 非空，此列必须有值，不能出现 NULL。

# 员工的名字和地址必须有值，不能为 NULL
CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    empName VARCHAR(20) NOT NULL,  # 约束名字这一列必须有值
    address VARCHAR(50) NOT NULL
)CHARSET = utf8;

INSERT INTO emp(empName, address) VALUES('', ''); # 空字符串，正确！
INSERT INTO emp(empName, address) VALUES('小黑', '他家地址');
INSERT INTO emp(empName, address) VALUES(NULL, NULL); # error: 不能为 NULL

1.2.2 默认值约束

关键字：DEFAULT 值 为列赋予默认值，当新增数据不指定值时，书写 DEFAULT，以指定的默认值进行填充

# 当存储性别的时候，给定了默认性别，可以使用 DEFAULT 也可以填入指定值
CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    empName VARCHAR(20) NOT NULL,
    address VARCHAR(50) NOT NULL,
    sex CHAR(1) DEFAULT '女'
)CHARSET = utf8;

INSERT INTO emp(empName, address, sex) VALUES('小黑', '他家地址', DEFAULT); # 取默认值
INSERT INTO emp(empName, address, sex) VALUES('小黑', '他家地址', '男');

1.2.3 引用完整性约束

语法：CONSTRAINT 引用名 FOREIGN KEY(列名) REFERENCES 被引用表名(列名)

详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是被引用表中存在的值。

#创建专业表
CREATE TABLE Speciality(
	id INT PRIMARY KEY AUTO_INCREMENT,
	SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

#创建课程表(课程表的SpecialId 引用专业表的 id)
CREATE TABLE subject(
    subjectId INT PRIMARY KEY AUTO_INCREMENT,
    subjectName VARCHAR(20) UNIQUE NOT NULL,
    subjectHours INT DEFAULT 20,
    specialId INT NOT NULL,
    CONSTRAINT fk_subject_specialId  FOREIGN KEY(specialId) REFERENCES Speciality(id)  #引用专业表里的 id 作为外键，新增课程信息时，约束课程所属的专业。
)charset=utf8;

#专业表新增数据
INSERT INTO Speciality(SpecialName) VALUES('Java');
INSERT INTO Speciality(SpecialName) VALUES('C#');
#课程信息表添加数据
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',30,1);#专业 id 为 1，引用的是专业表的 Java
INSERT INTO subject(subjectName,subjectHours) VALUES('C#MVC',10,2);#专业 id 为 2，引用的是专业表的 C#
INSERT INTO `subject`(subjecName, subjectHours, specialId) VALUES('BIGDATA', 30, 3); # error: 主键表不存在 3 所以不能插入 3

• 注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表），再删除主表（被引用表）

1.3 约束创建整合案例

创建带有约束的表。

1.3.1 创建表

列名	数据类型	约束	说明
GradeId	INT	主键、自动增长	班级编号
GradeName	VARCHAR(20)	唯一、非空	班级名称

CREATE TABLE grade(
	GradeId INT PRIMARY KEY AUTO_INCREMENT,
	GradeName VARCHAR(20)
)CHARSET = utf8;
SELECT * FROM grade;

INSERT INTO grade(GradeName) VALUES('class01'); # 主键 1
INSERT INTO grade(GradeName) VALUES('class02'); # 主键 2
INSERT INTO grade(GradeName) VALUES('class03'); # 主键 3

列名	数据类型	约束	说明
student_id	VARCHAR(50)	主键	学号
Student_name	VARCHAR(50)	非空	姓名
sex	CHAR(2)	默认填充’男’	性别
borndate	DATE	非空	生日
phone	VARCHAR(11)	无	电话
GradeId	INT	非空，外键约束：引用班级表的 gradeid。	班级编号

CREATE TABLE student(
  student_id VARCHAR(50) PRIMARY KEY,
  student_name VARCHAR(50) NOT NULL,
  sex CHAR(2) DEFAULT '男',
  borndate DATE NOT NULL,
  phone VARCHAR(11),
  gradeId INT NOT NULL,
  CONSTRAINT fk_student_gradeId FOREIGN KEY(gradeId) REFERENCES Grade(GradeId) # 引用Grade表的GradeId列的值作为外键，插入时约束学生的班级编号必须存在。
)CHARSET = utf8;
SELECT * FROM student;

INSERT INTO student(student_id, student_name, sex, borndate, phone, gradeId) VALUES('c01', '小明', DEFAULT, '2020-01-01', NULL, 1);
INSERT INTO student(student_id, student_name, sex, borndate, phone, gradeId) VALUES('c02', '小红', '女', '2020-01-02', NULL, 2);
INSERT INTO student(student_id, student_name, sex, borndate, phone, gradeId) VALUES('c03', '小张', DEFAULT, '2020-01-03', NULL, 2);
INSERT INTO student(student_id, student_name, sex, borndate, phone, gradeId) VALUES('c04', '小黑', DEFAULT, '2020-01-04', NULL, 4);  # 班级里不存在主键 4 添加 error

• 注意：创建关系表时，一定要先创建主表，再创建从表; 删除关系表时，先删除从表，再删除主表。