Coursera 笔记

Module 02 · Introduction to Relational Databases and Tables

Reading: Understanding Relational Model Constraints阅读材料：理解关系模型约束

Coursera ↗

Estimated time: 15 minutes
预计时间：15 分钟

Objectives 目标

After completing this reading, you will be able to:
完成本阅读后，您将能够：

  • Define and identify entity integrity, referential integrity, and domain integrity constraints
    定义并识别实体完整性、参照完整性和域完整性约束
  • Explain how each constraint maintains data integrity
    解释每种约束如何维护数据完整性
  • Recognize examples of how these constraints are implemented in SQL
    识别这些约束在 SQL 中实现的示例

Overview 概述

In any well-designed database, maintaining data integrity is essential to ensure the accuracy, consistency, and reliability of the stored information. This lab focuses on three key relational model constraints:
在任何精心设计的数据库中，维护数据完整性对于确保存储信息的准确性、一致性和可靠性至关重要。本实验聚焦于三种关键的关系模型约束：

  • Entity Integrity  实体完整性
  • Referential Integrity  参照完整性
  • Domain Integrity  域完整性

These constraints enforce rules on how data is stored and related within tables.
这些约束强制规定了数据在表内存储和关联的方式。

Imagine you are building a Database BookShopDB. You need to ensure that:
假设你正在构建一个数据库 BookShopDB。你需要确保：

  • Each book and author has a unique identifier.
    每本书和每位作者都有一个唯一的标识符。
  • Every book must be linked to a valid author.
    每本书都必须链接到一个有效的作者。
  • Attributes like price, title, and date must have values within an acceptable range and format.
    价格、标题和日期等属性必须具有在可接受范围内和格式的值。

This reading will walk you through these constraints with examples to solidify your understanding.
本阅读将通过示例引导您理解这些约束。

Sample view of the tables used in this reading 本阅读中使用的表格的示例视图

1. BookShop table 1. BookShop 表

This is a sample table structure and data view for the BookShop table used in this reading.
这是一个用于本阅读的 BookShop 表的示例表结构和数据视图。

2. BookShop_AuthorDetails table 2. BookShop_AuthorDetails 表

This is a sample table structure and data view for the BookShop_AuthorDetails table used in this reading.
这是一个用于本阅读的 BookShop_AuthorDetails 表的示例表结构和数据视图。

Types of relational model constraints 关系模型约束类型

Entity integrity constraint 实体完整性约束

This constraint ensures that every table in a relational database has a primary key. A primary key uniquely identifies each row in the table. A primary key column(s):
该约束确保关系数据库中的每个表都有一个主键。主键唯一标识表中的每一行。主键列（s）：

  • must not contain NULL values
    不得包含空值
  • must be unique across all rows
    在所有行中必须唯一

This constraint guarantees that each record (or entity) in a table is distinct and identifiable, preventing duplication and missing identifiers.
这一约束保证了表中每条记录（或实体）的唯一性和可识别性，防止重复和标识符丢失。

Example: 示例

CREATE TABLE BookShop (
    BOOK_ID INT PRIMARY KEY,
    TITLE VARCHAR(100),
    AUTHOR_ID INT
);

Explanation 解释

Here, BOOK_ID is the primary key. It must be:
这里，BOOK_ID 是主键。它必须是：

  • Unique (no two books can have the same ID)
    唯一性（没有两本书可以具有相同的 ID）
  • Not NULL (every book must have an ID)
    非空（每本书都必须有一个 ID）

Note: Every table in a relational database should have a primary key to satisfy entity integrity.
注意：关系数据库中的每个表都应该有一个主键，以满足实体完整性。

Referential integrity constraint 参照完整性约束

This constraint ensures that a foreign key in one table always refers to a valid primary key in another table. This maintains consistent and meaningful relationships between tables.
此约束确保一个表中的外键始终指向另一个表中的有效主键。这维护了表之间的一致和有意义的关系。

It enforces the logical link between related data in different tables, preventing the existence of invalid or "orphaned" references.
它强制执行不同表中相关数据之间的逻辑联系，防止无效或"孤儿"引用的存在。

Example 示例

CREATE TABLE BookShop_AuthorDetails (
    AUTHOR_ID INT PRIMARY KEY,
    AUTHOR_NAME VARCHAR(100)
);

CREATE TABLE BookShop (
    BOOK_ID INT PRIMARY KEY,
    TITLE VARCHAR(100),
    AUTHOR_ID INT,
    FOREIGN KEY (AUTHOR_ID) REFERENCES BookShop_AuthorDetails(AUTHOR_ID)
);

Explanation 解释

AUTHOR_ID in BookShop references AUTHOR_ID in BookShop_AuthorDetails.
AUTHOR_ID 在 BookShop 中引用 BookShop_AuthorDetails. 中的 AUTHOR_ID

This means every AUTHOR_ID in BookShop must exist in BookShop_AuthorDetails.
这意味着 BookShop 中的每个 AUTHOR_ID 必须存在于 BookShop_AuthorDetails.

Trying to insert a book with an AUTHOR_ID that doesn't exist in BookShop_AuthorDetails will fail.
尝试插入一个在 BookShop_AuthorDetails 中不存在的 AUTHOR_ID 的书籍将失败。

Domain integrity constraint 域完整性约束

This constraint ensures that all values stored in a column fall within a defined domain. This includes rules about:
此约束确保列中存储的所有值都在定义的域内。这包括以下规则：

  • Data type  数据类型
  • Format  格式
  • Allowed values  允许的值
  • Nullability  空值性

It helps ensure that data in a column is valid, logical, and consistent with its intended use.
它有助于确保列中的数据是有效的、逻辑上合理的，并且与其预期用途一致。

Example: 示例

CREATE TABLE BookShop (
    BOOK_ID INT PRIMARY KEY,
    TITLE VARCHAR(100) NOT NULL,
    PRICE DECIMAL(5, 2) CHECK (PRICE >= 0),
    PUBLISHED_DATE DATE
);

Explanation 解释

Two standard mechanisms used to enforce domain integrity are the CHECK and NOT NULL constraints:
两种用于强制执行域完整性的标准机制是 CHECK 和 NOT NULL 约束：

1. CHECK constraint 1. CHECK 约束

The CHECK constraint enforces rules about the range or pattern of acceptable values within a column. It ensures that the data entered meets specific logical conditions.
CHECK 约束强制执行列中可接受值的范围或模式的规则。它确保输入的数据满足特定的逻辑条件。

  • The PRICE column in the example above has a CHECK constraint to ensure it's not negative.
    上述示例中的 PRICE 列有一个 CHECK 约束，以确保它不是负数。
2. NOT NULL constraint 2. 非空约束

The NOT NULL constraint enforces nullability rules, ensuring that a column must contain a value and cannot be left empty (NULL).
非空约束强制执行空值规则，确保某一列必须包含值且不能为空（NULL）。

  • TITLE is NOT NULL. A title must be provided.
    TITLE 是非空约束。必须提供标题。

Summary 摘要

In this reading, you explored three key types of constraints used in relational databases. By applying these constraints, you can create accurate, consistent, and easy-to-manage databases.
在本阅读中，你探讨了关系数据库中使用的三种关键类型的约束。通过应用这些约束，你可以创建准确、一致且易于管理的数据库。