一文带你理清数据库的安全与保护

数据库的安全与保护

第一节 数据完整性

概述

    数据库的完整性是指数据库中数据的正确性和相容性。
    例如，人的性别只能是“男”或“女”，每个人的身份证号必须唯一等。

一、完整性约束条件的作用对象

完整性约束条件的作用对象可以是列、元组和表

1.列级约束

列级约束主要对列的类型、取值范围、精度等的约束，具体包括如下内容：

（1）对数据类型的约束，其包括数据类型、长度、精度

-- 比如 这里 cust_address 字段规定数据类型为定长字符型，且长度为50。
    CREATE TABLE mysql_test.customers (
    cust_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '客户号',
    cust_name char(50) NOT NULL,
    cust_sex char(1) not null default 0,
    cust_address char(50) null,    
    cust_contact char(50) null
    );

（2）对数据格式的约束
例如，如果有一张tb_student学生表，可规定studentNO学号字段的前四位为学生的入学年份，第5位规定为院系的编号等，就和我们身份证号中一样有出生年份和日期格式的规定一样。
（3）对取值范围或取值集合的约束。
例如，在学生成绩表中规定成绩字段的取值范围为0到100.
（4）对空值的约束。
例如，在定义列时规定该列是否允许取空值
如上面在创建customers表时定义cust_name 字段不能为空
代码片段如下：

cust_name char(50) NOT NULL,

2.元组的约束

在二维关系表中表中的一行数据即为元组

元组约束指若干元组之间、关系之间的联系的约束。

以 “学生（学号，姓名，年龄）” 关系为例，假设学校规定学生的年龄范围在 10 岁到 30 岁之间。那么在这个关系中的每一个元组，其年龄属性的值都必须满足这个范围要求。如果出现一个学生元组的年龄是 5 岁或者 35 岁，就违反了这个范围约束。这种约束是根据实际业务规则来定义的，能够保证数据符合业务逻辑，防止不合理的数据进入系统。

3.表级约束

表级约束指若干元组之间、关系之间的相互约束。
背景： 
    以学生信息管理系统为例，假设有一个 “学生（学号，姓名，性别，年龄）” 关系。学号是这个关系的主键。
解释：
    主键唯一地标识关系中的每一个元组。对于 “学生” 关系中的每一个元组，学号的值不能为 NULL（空值），并且不能有两个元组的学号是相同的。
这就保证了每个学生实体（元组）在关系中是唯一可识别的。
例如，不能有两个元组都表示学号为 “2024001” 的学生，因为这样就无法准确区分这两个所谓的 “同一学号” 的学生个体。

二、定义与实现完整性约束

三类完整性约束，分别是实体完整性、参照完整性和用户定义的完整性

1.实体完整性

在mysql中，实体完整性是通过主键约束和候选健约束来实现的
（1）主键约束
主键可以是表中的某一列，也可以是表中多个列所构成的一个组合。
其中多个列组合而成的主键也称为复合主键
在mysql中，主键列必须遵守如下一些规则：

• 每一个表只能定义一个主键
• 主键的值，也称为键值，必须能够唯一标志表中的每一行记录，且不能为NULL。表中两个不同的行在主键上不能具有相同的值。这是唯一性原则。
• 复合主键不能包含不必要的多余列。也就是说，当从一个复合主键中删除一列后，如果剩下的列构成主键仍能满足唯一性原则，那么这个复合主键是不正确的。这是最小化规则。
• 一个列名在复合主键的列表中只能出现一次
  主键约束定义方法：
  主键约束可以在CREATE TABLE或ALTER TABLE语句中使用关键字“PRIMARY KEY”来实现，其方式有两种。
  （1）一种是作为列的完整性约束
  如下：直接在列属性student_id字段后添加一条PRIMARY KEY即可
  示例1：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT
);

这里PRIMARY KEY关键字紧跟在student_id列的定义后面，表示student_id是该表的主键。这种方式适用于主键由单个列构成的简单情况。

在已存在的表中添加主键约束(ALTER TABLE语句)：
示例2：

ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

（2）一种作为表的完整性约束
示例：

CREATE TABLE course_registrations (
    student_id INT,
    course_id INT,
    registration_date DATE,
    CONSTRAINT pk_course_registrations PRIMARY KEY (student_id, course_id)
);

在这个例子中，CONSTRAINT关键字用于定义一个名为pk_course_registrations的约束，后面的PRIMARY KEY (student_id, course_id)表示student_id和course_id这两个列组合起来构成了该表的主键。
这种方式使得在多个列作为主键时，能够清晰地定义主键约束。

（2）候选健约束
与主键一样候选键可以是表中的某一列，也可以是表中多个列构成的一个组合。
任何时候候选键必须是唯一的，且不能为NULL。

候选键可以在CREATE TABLE或ALTER TABLE语句中使用关键字“UNIQUE”来实现，其实现方法与主键约束类似，同样可作为列或者表的完整性约束两种方式。

• 使用UNIQUE关键字和NOT NULL组合（单属性候选键） 假设要创建一个名为employees的表，其中employee_email可以作为一个候选键。SQL 语句如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_email VARCHAR(100) UNIQUE NOT NULL,
    employee_name VARCHAR(50),
    department VARCHAR(50)
);

在这里，employee_email列使用了UNIQUE关键字保证其值的唯一性，并且使用NOT NULL关键字保证其值不能为空。这样employee_email就满足了候选键的条件，可以用来唯一地标识元组，仅次于主键（employee_id）作为另一种识别方式。

• 使用CONSTRAINT关键字定义组合候选键（多属性候选键） 例如，创建一个名为project_members的表，project_id和member_id组合起来可以作为一个候选键。SQL 语句如下：

CREATE TABLE project_members (
    project_id INT,
    member_id INT,
    member_role VARCHAR(30),
    CONSTRAINT uk_project_members UNIQUE (project_id, member_id)
);

在这个例子中，通过CONSTRAINT关键字定义了一个名为uk_project_members（可以根据自己的喜好命名约束名称）的约束，UNIQUE (project_id, member_id)表示project_id和member_id这两个属性组合起来具有唯一性，这就构成了一个组合候选键。当主键不能完全满足数据识别需求时，这种组合候选键可以提供额外的、唯一的识别方式。

mysql中主键与候选键的异同：

1. 定义和功能 （1）候选键也是能够唯一标识表中每行数据的键或键组合。与主键不同的是，一个表可以有多个候选键，候选键提供了备用的、也能够唯一标识元组的方式，相当于给数据库记录的识别提供了多种 “选择”。 （2）定义主键约束时，系统会自动产生PRIMARY KEY索引，而定义候选键约束时， 系统自动产生UNIQUE索引。
2. 约束强度和用途

• 主键 主键具有更强的约束性。它不仅要求键值的唯一性，还要求键值不能为空（NULL）。在数据库操作中，主键通常是关联其他表的主要依据。例如，在一个 “订单（订单编号，客户编号，订单金额）” 表和一个 “客户（客户编号，客户姓名，客户地址）” 表的关系中，“订单” 表中的客户编号作为外键关联 “客户” 表的客户编号（主键），这种关联主要是基于主键的稳定性和唯一性构建的。
• 候选键 候选键的约束强度主要体现在唯一性上，虽然有些情况下也要求候选键不为空，但这不是绝对的（具体取决于数据库设计和业务需求）。候选键更多地是作为一种备用的识别机制。例如，在数据录入或者查询过程中，如果主键出现问题或者在某些特定的业务场景下，候选键可以用来替代主键进行数据的定位和操作。比如，当需要验证员工身份信息时，除了通过员工编号（主键），也可以通过身份证号码（候选键）来进行验证。