当前位置：首页 > article >正文

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

article 2024/10/2 23:14:33

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)
);