【PostgreSQL】入门篇——介绍表的创建、主键、外键、唯一约束和检查约束的概念及其应用

1. 表的创建

描述

表是数据库中用于存储数据的基本结构。每个表由行和列组成，行表示记录，列表示字段。每个字段有特定的数据类型（如整数、字符串、日期等），并且可以设置各种约束来确保数据的完整性。

语法

CREATE TABLE table_name (column1 datatype [constraint],column2 datatype [constraint],...
);

应用场景

数据存储：表是存储各种数据的基本单位，几乎所有的数据库操作都涉及到表。
数据组织：通过表可以有效地组织和管理数据。

示例

创建一个名为 employees 的表，包含员工的基本信息：

CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50),salary NUMERIC(10, 2)
);

解释

employee_id SERIAL PRIMARY KEY：定义了一个自增的主键，用于唯一标识每个员工。
first_name、last_name、department：存储员工的名字、姓氏和部门，使用 VARCHAR 数据类型。
salary NUMERIC(10, 2)：存储员工的工资，最多 10 位数字，其中 2 位为小数。

2. 主键 (Primary Key)

描述

主键是表中用于唯一标识每一行记录的字段或字段组合。主键的值必须是唯一的，并且不能为空。主键的主要作用是确保数据的唯一性和完整性。

应用场景

唯一标识：每个记录必须有一个唯一的标识符，主键正是用于这个目的。
数据完整性：通过主键可以确保没有重复的记录。

优缺点

优点：确保数据的唯一性，提升查询效率。
缺点：主键一旦设置，无法更改，可能导致数据迁移时的复杂性。

示例

在 employees 表中，employee_id 是主键，使用 SERIAL 数据类型自动生成唯一值。

CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50),salary NUMERIC(10, 2)
);

解释

PRIMARY KEY 约束确保 employee_id 的值是唯一的，不能重复。

3. 外键 (Foreign Key)

描述

外键是一个表中的字段，它引用另一个表的主键。外键用于建立表之间的关系，确保数据的参照完整性。外键约束确保在子表中插入的值必须在父表中存在。

应用场景

表关系：用于表示表之间的关系，如订单与客户、员工与部门等。
数据完整性：确保在子表中引用的值在父表中是有效的。

优缺点

优点：维护数据的一致性，防止无效数据的插入。
缺点：可能影响性能，特别是在涉及大量数据的情况下。

示例

创建一个 departments 表，并在 employees 表中添加外键，引用 departments 表的主键：

CREATE TABLE departments (department_id SERIAL PRIMARY KEY,department_name VARCHAR(50) UNIQUE
);CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,salary NUMERIC(10, 2),FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

解释

FOREIGN KEY (department_id) REFERENCES departments(department_id)：这条语句确保 employees 表中的 department_id 必须在 departments 表中存在。

4. 唯一约束 (Unique Constraint)

描述

唯一约束确保某一列或多列的值在表中是唯一的。允许空值，但每个非空值必须是唯一的。唯一约束可以应用于多个列的组合。

应用场景

防止重复数据：确保某些字段（如电子邮件、用户名）在表中是唯一的。
组合唯一性：可以对多个列组合设置唯一约束。

优缺点

优点：确保数据的唯一性，避免重复记录。
缺点：可能会影响插入性能，尤其是在高并发情况下。

示例

在 employees 表中，添加唯一约束以确保每个员工的电子邮件地址是唯一的：

CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100) UNIQUE,department_id INT,salary NUMERIC(10, 2),FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

解释

UNIQUE 约束确保 email 字段的值在 employees 表中是唯一的。

5. 检查约束 (Check Constraint)

描述

检查约束用于限制列中的值，确保它们满足特定条件。可以用于确保数据的有效性，如数值范围、字符串格式等。

应用场景

数据有效性：确保插入的数据符合业务规则。
范围限制：限制数值在特定范围内。

优缺点

优点：增强数据的完整性，防止无效数据的插入。
缺点：复杂的检查条件可能影响性能。

示例

在 employees 表中，添加检查约束以确保员工的工资大于零：

CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100) UNIQUE,department_id INT,salary NUMERIC(10, 2) CHECK (salary > 0),FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

解释

CHECK (salary > 0) 约束确保 salary 字段的值必须大于零，防止插入无效的工资数据。

总结

在数据库设计中，合理使用表、主键、外键、唯一约束和检查约束，可以确保数据的完整性和一致性。以下是这些概念的简要总结：

表的创建：定义数据的存储结构。
主键：唯一标识表中的每一行记录，确保数据的唯一性。
外键：建立表之间的关系，确保参照完整性，防止无效数据。
唯一约束：确保某一列的值在表中是唯一的，防止重复数据。
检查约束：限制列中的值以确保数据的有效性。

通过这些约束，数据库能够更好地维护数据的完整性和一致性，防止无效或重复的数据被插入。