数据库系统第8节关系完整性约束

最新推荐文章于 2024-09-14 19:55:29 发布

hummhumm

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量689

点赞数 28

文章标签：数据库 java 开发语言 python 数据库架构后端 mysql

本文链接：https://blog.csdn.net/hummhumm/article/details/141089720

版权

关系完整性约束是数据库设计中非常重要的概念，它们确保了数据的一致性和准确性。以下是关于关系完整性约束的详细介绍：

实体完整性 (Entity Integrity)

实体完整性规定了每个表都必须有一个主键，该主键用来唯一标识表中的每一行记录。这意味着：

主键不能为 NULL。
主键的值必须是唯一的。

示例

假设我们有一个名为 employees 的表，其中包含以下列：

employee_id (整数) - 主键
first_name (字符串)
last_name (字符串)
department_id (整数)

为了定义 employee_id 作为主键，可以在创建表时使用如下 SQL 语句：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

参照完整性 (Referential Integrity)

参照完整性确保了外键与主键之间的关联一致性。当一个表中的外键引用另一个表的主键时，必须遵守以下规则：

外键的值必须与另一个表中的主键值匹配。
或者外键的值可以是 NULL（如果允许 NULL 值的话）。

示例

如果我们有另一个名为 departments 的表，其中包含以下列：

department_id (整数) - 主键
department_name (字符串)

我们可以定义 employees 表中的 department_id 列为外键，引用 departments 表的 department_id 主键：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在这个例子中，如果尝试插入一个不存在于 departments 表中的 department_id 值到 employees 表中，将会违反参照完整性规则，并导致错误。

域完整性 (Domain Integrity)

域完整性指的是对于表中列的值的约束，包括但不限于：

数据类型
格式
长度
取值范围
默认值
是否允许 NULL 值

示例

假设我们想要限制 employees 表中的 first_name 和 last_name 字段只能包含字母，并且长度不超过 50 个字符，可以这样定义：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL CHECK (first_name ~ '^[A-Za-z ]+$'),
    last_name VARCHAR(50) NOT NULL CHECK (last_name ~ '^[A-Za-z ]+$'),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在这个例子中，CHECK 约束使用正则表达式来验证 first_name 和 last_name 是否只包含字母和空格。

通过这些完整性约束，我们可以确保数据库中的数据保持一致性和有效性，从而避免了许多潜在的数据问题。

这次我们将使用一个新的案例来说明如何在数据库设计中应用关系完整性约束。假设我们正在为一家在线书店设计一个简单的数据库系统。

在线书店数据库案例

目标

设计一个能够支持基本书籍销售功能的数据库。
确保数据的完整性和一致性。

数据库设计

我们将设计以下表格：

Books - 存储书籍信息。
Authors - 存储作者信息。
Publishers - 存储出版商信息。
Orders - 存储订单信息。
Order_Items - 存储订单中的书籍详情。

表格结构

Books
- book_id (INT, Primary Key, Auto Increment)
- title (VARCHAR)
- author_id (INT, Foreign Key referencing Authors(author_id))
- publisher_id (INT, Foreign Key referencing Publishers(publisher_id))
- price (DECIMAL)
- publication_date (DATE)
Authors
- author_id (INT, Primary Key, Auto Increment)
- name (VARCHAR)
- bio (TEXT)
Publishers
- publisher_id (INT, Primary Key, Auto Increment)
- name (VARCHAR)
- location (VARCHAR)
Orders
- order_id (INT, Primary Key, Auto Increment)
- customer_name (VARCHAR)
- order_date (DATE)
Order_Items
- order_item_id (INT, Primary Key, Auto Increment)
- order_id (INT, Foreign Key referencing Orders(order_id))
- book_id (INT, Foreign Key referencing Books(book_id))
- quantity (INT)
- price (DECIMAL)

实体完整性 (Entity Integrity)

实体完整性要求每个表都有一个主键来唯一标识每一条记录。

示例

对于 Books 表，book_id 是主键，因此：

每本书都有一个唯一的 book_id。
book_id 不能为空。

对于 Authors 表，author_id 是主键，因此：

每个作者都有一个唯一的 author_id。
author_id 不能为空。

对于 Publishers 表，publisher_id 是主键，因此：

每个出版商都有一个唯一的 publisher_id。
publisher_id 不能为空。

对于 Orders 表，order_id 是主键，因此：

每个订单都有一个唯一的 order_id。
order_id 不能为空。

对于 Order_Items 表，order_item_id 是主键，因此：

每个订单项都有一个唯一的 order_item_id。
order_item_id 不能为空。

参照完整性 (Referential Integrity)

参照完整性确保了外键与主键之间的关联一致性。

示例

对于 Books 表，author_id 和 publisher_id 分别是外键，因此：

author_id 必须与 Authors 表中的某个 author_id 匹配。
publisher_id 必须与 Publishers 表中的某个 publisher_id 匹配。

对于 Order_Items 表，order_id 和 book_id 分别是外键，因此：

order_id 必须与 Orders 表中的某个 order_id 匹配。
book_id 必须与 Books 表中的某个 book_id 匹配。

域完整性 (Domain Integrity)

域完整性定义了列的有效值范围或格式。

示例

对于 Books 表：

price 必须是非负数值。
publication_date 必须是一个有效的日期。

对于 Authors 表：

name 必须是非空字符串。

对于 Publishers 表：

name 必须是非空字符串。

对于 Orders 表：

order_date 必须是一个有效的日期。

对于 Order_Items 表：

quantity 必须是非负整数。
price 必须是非负数值。

创建表的 SQL 语句示例

以下是创建这些表的 SQL 语句示例：

CREATE TABLE Authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    bio TEXT
);

CREATE TABLE Publishers (
    publisher_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location VARCHAR(255)
);

CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    publisher_id INT,
    price DECIMAL(10, 2) NOT NULL,
    publication_date DATE NOT NULL,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id),
    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
);

CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    order_date DATE NOT NULL
);

CREATE TABLE Order_Items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    book_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

这些完整性约束确保了数据的一致性和准确性，并有助于防止不一致的数据输入到数据库中。

这次我们将探讨一个关于大学课程管理系统的新案例，这个系统需要处理学生、教师、课程和成绩等信息。我们将重点关注如何在设计数据库时应用关系完整性约束。

大学课程管理系统案例

目标

管理学生的注册信息。
记录课程和教师的信息。
管理学生的成绩记录。
确保数据的完整性和一致性。

数据库设计

我们将设计以下表格：

Students - 存储学生信息。
Teachers - 存储教师信息。
Courses - 存储课程信息。
Enrollments - 存储学生选课信息。
Grades - 存储学生的成绩记录。

表格结构

Students
- student_id (INT, Primary Key, Auto Increment)
- first_name (VARCHAR)
- last_name (VARCHAR)
- email (VARCHAR, Unique)
Teachers
- teacher_id (INT, Primary Key, Auto Increment)
- first_name (VARCHAR)
- last_name (VARCHAR)
- email (VARCHAR, Unique)
Courses
- course_id (INT, Primary Key, Auto Increment)
- course_code (VARCHAR, Unique)
- course_name (VARCHAR)
- teacher_id (INT, Foreign Key referencing Teachers(teacher_id))
Enrollments
- enrollment_id (INT, Primary Key, Auto Increment)
- student_id (INT, Foreign Key referencing Students(student_id))
- course_id (INT, Foreign Key referencing Courses(course_id))
Grades
- grade_id (INT, Primary Key, Auto Increment)
- enrollment_id (INT, Foreign Key referencing Enrollments(enrollment_id))
- grade (DECIMAL)

实体完整性 (Entity Integrity)

实体完整性要求每个表都有一个主键来唯一标识每一条记录。

示例

对于 Students 表，student_id 是主键，因此：
- 每个学生都有一个唯一的 student_id。
- student_id 不能为空。
对于 Teachers 表，teacher_id 是主键，因此：
- 每个教师都有一个唯一的 teacher_id。
- teacher_id 不能为空。
对于 Courses 表，course_id 是主键，因此：
- 每门课程都有一个唯一的 course_id。
- course_id 不能为空。
对于 Enrollments 表，enrollment_id 是主键，因此：
- 每次选课都有一个唯一的 enrollment_id。
- enrollment_id 不能为空。
对于 Grades 表，grade_id 是主键，因此：
- 每个成绩记录都有一个唯一的 grade_id。
- grade_id 不能为空。

参照完整性 (Referential Integrity)

参照完整性确保了外键与主键之间的关联一致性。

示例

对于 Courses 表，teacher_id 是外键，因此：
- teacher_id 必须与 Teachers 表中的某个 teacher_id 匹配。
对于 Enrollments 表，student_id 和 course_id 分别是外键，因此：
- student_id 必须与 Students 表中的某个 student_id 匹配。
- course_id 必须与 Courses 表中的某个 course_id 匹配。
对于 Grades 表，enrollment_id 是外键，因此：
- enrollment_id 必须与 Enrollments 表中的某个 enrollment_id 匹配。

域完整性 (Domain Integrity)

域完整性定义了列的有效值范围或格式。

示例

对于 Students 表：
- email 必须是唯一的，并且格式正确。
对于 Teachers 表：
- email 必须是唯一的，并且格式正确。
对于 Courses 表：
- course_code 必须是唯一的。
对于 Enrollments 表：
- 无特殊域完整性约束。
对于 Grades 表：
- grade 必须是非负数值，并且通常在 0 到 100 之间。

创建表的 SQL 语句示例

以下是创建这些表的 SQL 语句示例：

CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE Teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE Courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_code VARCHAR(255) UNIQUE NOT NULL,
    course_name VARCHAR(255) NOT NULL,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

CREATE TABLE Enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE Grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,
    enrollment_id INT,
    grade DECIMAL(4, 2) CHECK (grade BETWEEN 0 AND 100),
    FOREIGN KEY (enrollment_id) REFERENCES Enrollments(enrollment_id)
);