PostgreSQL教程FG023-PG表设计基础：字段类型选择与约束设置（主键/外键/非空）

本文档风哥主要介绍PostgreSQL教程023相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

内容简介：本文介绍PostgreSQL数据库的表设计基础，包括字段类型选择和约束设置（主键、外键、非空）。风哥教程参考PostgreSQL官方文档Data Definition、Data Types等内容，详细讲解表设计的核心原则和方法，帮助读者创建结构合理、数据完整的数据库表。

Part01-基础概念与理论知识

1.1 表设计概述

表设计是数据库设计的核心，合理的表设计可以提高数据的完整性、一致性和查询效率。表设计包括以下几个方面：

更多视频教程www.fgedu.net.cn

确定表的结构和关系
选择合适的字段类型
设置适当的约束
创建必要的索引

表设计应该遵循关系数据库的范式理论，确保数据的冗余度最小，同时满足业务需求。

1.2 PostgreSQL数据类型

PostgreSQL支持丰富的数据类型，主要包括：

数值类型：INTEGER, BIGINT, SMALLINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
字符类型：CHARACTER, VARCHAR, TEXT
日期/时间类型：DATE, TIME, TIMESTAMP, INTERVAL
布尔类型：BOOLEAN
二进制类型：BYTEA
复合类型：RECORD
枚举类型：ENUM
数组类型：如INTEGER[], VARCHAR[]
JSON类型：JSON, JSONB
几何类型：POINT, LINE, CIRCLE等

选择合适的数据类型可以提高数据存储效率和查询性能。

1.3 约束概述

约束是用于保证数据完整性的规则，PostgreSQL支持以下主要约束类型：

主键约束（PRIMARY KEY）：唯一标识表中的记录
外键约束（FOREIGN KEY）：建立表之间的关系
非空约束（NOT NULL）：确保字段值不为空
唯一约束（UNIQUE）：确保字段值唯一
检查约束（CHECK）：确保字段值满足指定条件
默认约束（DEFAULT）：为字段设置默认值

约束可以在创建表时定义，也可以在创建表后添加。

1.4 主键约束

主键约束用于唯一标识表中的每条记录，具有以下特点：

主键值必须唯一
主键值不能为空
每个表只能有一个主键
主键可以由一个或多个字段组成（复合主键）

主键通常使用SERIAL或BIGSERIAL类型的自增字段。

1.5 外键约束

外键约束用于建立表之间的关系，确保引用完整性，具有以下特点：

外键引用另一个表的主键或唯一约束
外键可以为空
一个表可以有多个外键
可以设置级联操作（CASCADE, SET NULL, SET DEFAULT, NO ACTION, RESTRICT）

外键约束有助于维护数据的一致性和完整性。

1.6 非空约束

非空约束用于确保字段值不为空，具有以下特点：

字段值不能为NULL
可以在创建表时或创建表后添加
有助于保证数据的完整性

非空约束是最基本的约束类型，用于确保必要的字段有值。

Part02-生产环境规划与建议

2.1 表设计原则

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 遵循关系数据库范式理论（1NF, 2NF, 3NF）
– 表名和字段名使用有意义的名称，遵循命名规范
– 每个表应该有一个主键
– 合理设计表之间的关系
– 避免过度设计和冗余字段
– 考虑未来的扩展性

2.2 数据类型选择最佳实践

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 选择最小的合适数据类型，提高存储效率
– 使用数值类型存储数字，避免使用字符类型
– 使用日期/时间类型存储日期和时间
– 对于变长字符串，使用VARCHAR类型
– 对于长文本，使用TEXT类型
– 对于JSON数据，使用JSONB类型（支持索引）
– 避免使用FLOAT和DOUBLE PRECISION存储精确数值，使用NUMERIC或DECIMAL

2.3 约束设置最佳实践

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 为每个表设置主键
– 合理使用外键约束，确保引用完整性
– 为必要的字段设置非空约束
– 使用唯一约束确保字段值的唯一性
– 使用检查约束验证字段值的有效性
– 使用默认约束为字段提供合理的默认值
– 避免过多的约束影响性能

Part03-生产环境项目实施方案

3.1 字段类型选择示例

以下是PostgreSQL中字段类型选择的示例。学习交流加群风哥微信: itpux-com

$ psql -d fgedudb -U fgedu
Password for fgedu fgedu:
psql (18.3)
Type “help” for help.

fgedudb=>

— 创建包含各种数据类型的表
CREATE TABLE fgedu_data_types_example (
id SERIAL PRIMARY KEY,
— 数值类型
integer_col INTEGER,
bigint_col BIGINT,
smallint_col SMALLINT,
numeric_col NUMERIC(10, 2),
real_col REAL,
double_col DOUBLE PRECISION,

— 字符类型
char_col CHAR(10),
varchar_col VARCHAR(100),
text_col TEXT,

— 日期/时间类型
date_col DATE,
time_col TIME,
timestamp_col TIMESTAMP,
interval_col INTERVAL,

— 布尔类型
boolean_col BOOLEAN,

— 二进制类型
bytea_col BYTEA,

— 数组类型
int_array_col INTEGER[],
varchar_array_col VARCHAR[],

— JSON类型
json_col JSON,
jsonb_col JSONB
);

— 删除测试表
DROP TABLE IF EXISTS fgedu_data_types_example;

DROP TABLE

3.2 主键约束实现

以下是主键约束的实现示例。

— 创建带有简单主键的表
CREATE TABLE fgedu_simple_primary_key (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
description TEXT
);

CREATE TABLE fgedu_– 创建带有复合主键的表
CREATE TABLE fgedu_composite_primary_key (
fgedu_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
purchase_date DATE NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (fgedu_id, product_id, purchase_date)
);

— 删除测试表
DROP TABLE IF EXISTS fgedu_simple_primary_key;
DROP TABLE IF EXISTS fgedu_composite_primary_key;

DROP TABLE
DROP TABLE

3.3 外键约束实现

以下是外键约束的实现示例。

— 创建父表
CREATE TABLE fgedu_departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50) NOT NULL,
location VARCHAR(100)
);

CREATE TABLE fgedu_– 创建带有外键的子表
CREATE TABLE fgedu_employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INTEGER,
hire_date DATE NOT NULL,
salary NUMERIC(10, 2) NOT NULL,
FOREIGN KEY (department_id) REFERENCES fgedu_departments(department_id) ON DELETE SET NULL
);

CREATE TABLE fgedu_– 创建带有级联删除的外键
CREATE TABLE fgedu_projects (
project_id SERIAL PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
department_id INTEGER NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
FOREIGN KEY (department_id) REFERENCES fgedu_departments(department_id) ON DELETE CASCADE
);

— 删除测试表
DROP TABLE IF EXISTS fgedu_projects CASCADE;
DROP TABLE IF EXISTS fgedu_employees CASCADE;
DROP TABLE IF EXISTS fgedu_departments CASCADE;

DROP TABLE
DROP TABLE
DROP TABLE

3.4 非空约束实现

以下是非空约束的实现示例。

from oracle:www.itpux.com

— 创建带有非空约束的表
CREATE TABLE fgedu_not_null_example (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
address TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

— 尝试插入空值（会失败）
INSERT INTO fgedu_not_null_example (name, email, phone, address)
VALUES (‘风哥1号’, NULL, ‘13800138000’, ‘北京市朝阳区’);
ERROR: null value in column “email” violates not-null constraint
DETAIL: Failing row contains (1, 风哥1号, null, 13800138000, 北京市朝阳区, 2026-04-02 16:30:45.678901+08).

— 插入有效数据
INSERT INTO fgedu_not_null_example (name, email, phone, address)
VALUES (‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’, ‘北京市朝阳区’),
(‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13900139000’, ‘上海市浦东新区’);

INSERT 0 2

— 删除测试表
DROP TABLE IF EXISTS fgedu_not_null_example;

DROP TABLE

Part04-生产案例与实战讲解

4.1 客户信息表设计

以下是客户信息表的设计示例。学习交流加群风哥QQ113257174

— 创建客户信息表
CREATE TABLE fgedu_customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20),
birth_date DATE,
gender VARCHAR(10),
registration_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_login_date TIMESTAMP,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT chk_gender CHECK (gender IN (‘男’, ‘女’, ‘其他’))
);

— 插入测试数据
INSERT INTO fgedu_customers (first_name, last_name, email, phone, birth_date, gender)
VALUES (‘张’, ‘三’, ‘zhangsan@fgedu.net.cn’, ‘13800138000’, ‘1990-01-01’, ‘男’),
(‘李’, ‘四’, ‘lisi@fgedu.net.cn’, ‘13900139000’, ‘1992-03-15’, ‘女’),
(‘王’, ‘五’, ‘wangwu@fgedu.net.cn’, ‘13700137000’, ‘1988-07-20’, ‘男’);

INSERT 0 3

— 查询数据
SELECT * FROM fgedu_customers;
customer_id | first_name | last_name | email | phone | birth_date | gender | registration_date | last_login_date | is_active
————-+————+———–+———————–+————–+————+——–+————————————-+—————–+———–+
1 | 张 | 三 | zhangsan@fgedu.net.cn | 13800138000 | 1990-01-01 | 男 | 2026-04-02 16:40:20.123456+08 | | t
2 | 李 | 四 | lisi@fgedu.net.cn | 13900139000 | 1992-03-15 | 女 | 2026-04-02 16:40:20.123456+08 | | t
3 | 王 | 五 | wangwu@fgedu.net.cn | 13700137000 | 1988-07-20 | 男 | 2026-04-02 16:40:20.123456+08 | | t
(3 rows)

4.2 订单表设计

以下是订单表的设计示例，包含与客户表的外键关系。

— 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES fgedu_customers(customer_id) ON DELETE CASCADE,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(10, 2) NOT NULL,
order_status VARCHAR(20) NOT NULL DEFAULT ‘pending’,
payment_method VARCHAR(50) NOT NULL,
shipping_address TEXT NOT NULL,
tracking_number VARCHAR(50),
CONSTRAINT chk_order_status CHECK (order_status IN (‘pending’, ‘processing’, ‘shipped’, ‘delivered’, ‘cancelled’))
);

— 插入测试数据
INSERT INTO fgedu_orders (customer_id, total_amount, payment_method, shipping_address)
VALUES (1, 199.98, ‘支付宝’, ‘北京市朝阳区某某街道1号’),
(2, 299.97, ‘微信支付’, ‘上海市浦东新区某某街道2号’),
(3, 99.99, ‘信用卡’, ‘广州市天河区某某街道3号’);

INSERT 0 3

— 查询数据
SELECT * FROM fgedu_orders;
order_id | customer_id | order_date | total_amount | order_status | payment_method | shipping_address | tracking_number
———-+————-+——————————-+————–+————–+—————-+————————–+—————-+
1 | 1 | 2026-04-02 16:45:35.678901+08 | 199.98 | pending | 支付宝 | 北京市朝阳区某某街道1号 |
2 | 2 | 2026-04-02 16:45:35.678901+08 | 299.97 | pending | 微信支付 | 上海市浦东新区某某街道2号 |
3 | 3 | 2026-04-02 16:45:35.678901+08 | 99.99 | pending | 信用卡 | 广州市天河区某某街道3号 |
(3 rows)

4.3 订单明细表设计

以下是订单明细表的设计示例，包含与订单表和产品表的外键关系。

— 创建产品表
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
stock INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE fgedu_– 创建订单明细表
CREATE TABLE fgedu_order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES fgedu_orders(order_id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES fgedu_products(product_id) ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0),
subtotal NUMERIC(10, 2) NOT NULL CHECK (subtotal > 0)
);

CREATE TABLE fgedu_– 插入产品数据
INSERT INTO fgedu_products (product_name, category, price, stock)
VALUES (‘PostgreSQL教程’, ‘书籍’, 99.99, 100),
(‘Python编程’, ‘书籍’, 89.99, 150),
(‘Java开发’, ‘书籍’, 109.99, 200),
(‘Linux运维’, ‘书籍’, 119.99, 120);

INSERT 0 4

— 插入订单明细数据
INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price, subtotal)
VALUES (1, 1, 1, 99.99, 99.99),
(1, 2, 1, 89.99, 89.99),
(2, 3, 1, 109.99, 109.99),
(2, 4, 1, 119.99, 119.99),
(2, 2, 1, 89.99, 89.99),
(3, 1, 1, 99.99, 99.99);

INSERT 0 6

— 查询订单明细数据
SELECT
oi.item_id,
o.order_id,
c.customer_id,
c.first_name || ‘ ‘ || c.last_name AS customer_name,
p.product_id,
p.product_name,
oi.quantity,
oi.unit_price,
oi.subtotal,
o.order_status
FROM fgedu_order_items oi
JOIN fgedu_orders o ON oi.order_id = o.order_id
JOIN fgedu_customers c ON o.customer_id = c.customer_id
JOIN fgedu_products p ON oi.product_id = p.product_id;
item_id | order_id | customer_id | customer_name | product_id | product_name | quantity | unit_price | subtotal | order_status
———+———-+————-+—————+————+————–+———-+————+———-+————–+
1 | 1 | 1 | 张三 | 1 | PostgreSQL教程 | 1 | 99.99 | 99.99 | pending
2 | 1 | 1 | 张三 | 2 | Python编程 | 1 | 89.99 | 89.99 | pending
3 | 2 | 2 | 李四 | 3 | Java开发 | 1 | 109.99 | 109.99 | pending
4 | 2 | 2 | 李四 | 4 | Linux运维 | 1 | 119.99 | 119.99 | pending
5 | 2 | 2 | 李四 | 2 | Python编程 | 1 | 89.99 | 89.99 | pending
6 | 3 | 3 | 王五 | 1 | PostgreSQL教程 | 1 | 99.99 | 99.99 | pending
(6 rows)

— 删除测试表
DROP TABLE IF EXISTS fgedu_order_items CASCADE;
DROP TABLE IF EXISTS fgedu_products CASCADE;
DROP TABLE IF EXISTS fgedu_orders CASCADE;
DROP TABLE IF EXISTS fgedu_customers CASCADE;

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

Part05-风哥经验总结与分享

风哥提示：表设计是数据库设计的基础，合理的表设计可以提高数据的完整性、一致性和查询效率。在进行表设计时，应该充分考虑业务需求、数据类型选择和约束设置等因素。

1. 表设计应该遵循关系数据库的范式理论，确保数据的冗余度最小，同时满足业务需求。

2. 选择合适的数据类型可以提高数据存储效率和查询性能，应该根据实际需求选择最小的合适数据类型。

3. 主键约束用于唯一标识表中的记录，每个表应该有一个主键，通常使用自增字段。

4. 外键约束用于建立表之间的关系，确保引用完整性，可以设置级联操作处理相关记录。

5. 非空约束用于确保必要的字段有值，是最基本的约束类型。

6. 在生产环境中，应该遵循表设计原则和最佳实践，创建结构合理、数据完整的数据库表。

7. 定期审查和优化表设计，根据业务需求的变化进行调整。

8. 使用约束可以提高数据的质量和完整性，减少数据错误和异常。

9. 合理设计表之间的关系，避免过度设计和复杂的连接查询。

10. 结合索引设计，提高查询性能，优化数据库整体性能。更多学习教程公众号风哥教程itpux_com

通过掌握表设计的基础知识和最佳实践，可以创建高效、可靠、可维护的数据库结构，为业务应用提供坚实的数据基础。from PostgreSQL:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html