PostgreSQL之外键约束

最新推荐文章于 2025-04-23 10:13:30 发布
最新推荐文章于 2025-04-23 10:13:30 发布
阅读量8.8k 收藏 7
点赞数 1
分类专栏： postgresql 文章标签： postgresql 数据库 python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_35462323/article/details/120534375
版权

外键

又称外键约束，Foreign key constraint。

外键是一个表中，用于标识另一张表中行的一个字段或多个字段。包含外键的表称为引用表，外键引用表称为被引用表。所谓外键约束是指引用字段必须在被引用字段中出现。被引用字段需要是唯一约束或主键。

外键约束维护引用表和被引用表之间的参照完整性（referential integrity）。

外键约束可以在创建表时定义，也可以在表创建后通过alter table语句定义。

定义外键约束的完整语法

<span style="color:#000000"><span style="background-color:#ffffff"><code>[ CONSTRAINT constraint_name ] FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]
</code></span></span>

其中：
constraint_name : 外键约束名称
( column_name [, … ] ) : 引用表中的引用字段
reftable : 被引用表
( refcolumn [, … ] ) : 被引用表中的被引用字段，和( column_name [, … ] )对应。
MATCH [SIMPLE|FULL] : 外键匹配模式,如果引用字段全部不是NULL,则强匹配,否则根据匹配模式进行弱匹配。

  • SIMPLE,默认值，只要引用字段中任一字段为NULL，则不要求与被引用字段强匹配；

  • FULL,只有引用字段全部为NULL，才不要求与被引用字段强匹配。

ON DELETE [CASCADE | NO ACTION] : 默认NO ACTION。

  • CASCADE,删除被引用表数据时级联删除引用表数据

  • NO ACTION,删除被引用表数据时必须先删除引用表数据，否则，如果引用表如果存在数据，直接删除被引用表数据返回失败。

ON UPDATE [CASCADE | NO ACTION] : 默认NO ACTION

  • CASCADE,更新被引用表时级联更新引用表数据

  • NO ACTION,更新被引用表时必须先删除引用表数据，否则，如果引用表存在数据，直接更新被引用表数据返回失败。

创建表时定义外键约束

创建一张t_currency表

<span style="color:#000000"><span style="background-color:#ffffff"><code>CREATE TABLE t_currency
(
    id      int,
    shortcut    char (3),
    PRIMARY KEY (id)
);
</code></span></span>

创建一张t_product表, 其中包含外键约束currency_id引用t_currency的id字段。pg中定义外键约束需要用到REFERENCES关键字。

<span style="color:#000000"><span style="background-color:#ffffff"><code>CREATE TABLE t_product
(
    id      int,
    name        text,
    currency_id int REFERENCES t_currency (id),
    PRIMARY KEY (id)
);

</code></span></span>

上面提到被引用字段需要时被引用表的主键和唯一约束。如果我们引用了非主键和唯一约束会发生什么？来看一下。 创建一张表t_product1,定义外键约束引用t_currency的shortcut，shortcut既不是主键，也不是唯一约束。

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># CREATE TABLE t_product1</em></span>
(
    id      int,
    name        text,
    currency_id int REFERENCES t_currency (shortcut),
    PRIMARY KEY (id)
);
ERROR:  there is no unique constraint matching given keys <span style="color:#c678dd">for</span> referenced table <span style="color:#98c379">"t_currency"</span>
</code></span></span>

可以看到创建失败，提示“there is no unique constraint matching given keys for referenced table "t_currency"”

创建完外键约束之后，t_product和t_currency之间的参照完整性就建立了，也就说我不能在t_product中插入一条curruncy_id非空但没有出现在t_currency的记录。

尝试在t_product表中插入一条记录，其中currency_id等于1,此时t_currency表中并没有id等于1的记录。

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(1) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
</code></span></span>

可以看到执行报错：

ERROR: insert or update on table "t_product" violates foreign key constraint "t_product_currency_id_fkey" DETAIL: Key (currency_id)=(1) is not present in table "t_currency".

这个点在实践场景是非常需要注意的，因为引用表和被引用表之间的参照完整性的存在，就已经确立了表记录的插入顺序。如果没有外键，则可以以任意顺序插入任何表，但有了外键，就需要保证正确的插入顺序。

定义多个字段组成的外键

创建一张表t_unique, 它包含唯一约束uk_tbl_unique_a_b(a,b)

<span style="color:#000000"><span style="background-color:#ffffff"><code>create table t_unique(
a int not null,
b int,
c varchar(10) not null default <span style="color:#98c379">'catch u'</span>,
constraint uk_tbl_unique_a_b unique(a,b)
);
</code></span></span>

创建一张表t_child，定义外键约束引用t_unique的a,b字段。

<span style="color:#000000"><span style="background-color:#ffffff"><code>CREATE TABLE t_child(
  c1 <span style="color:#e6c07b">integer</span> PRIMARY KEY,
  c2 <span style="color:#e6c07b">integer</span>,
  c3 <span style="color:#e6c07b">integer</span>,
  FOREIGN KEY (c2, c3) REFERENCES t_unique (a, b)
);
</code></span></span>

给已存在的表定义外键

使用 ALTER TABLE 给一个已存在的表定义外键。

示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code>ALTER TABLE t_child 
ADD CONSTRAINT fk_c1 FOREIGN KEY (c1) REFERENCES t_parent (p1);
</code></span></span>

删除外键

示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code>alter table t_child drop constraint fk_c1;
</code></span></span>

禁用外键

有时候我们想让外键暂时失效，而不是删除它，可以怎么做？

<span style="color:#000000"><span style="background-color:#ffffff"><code>alter table t_product <span style="color:#e6c07b">disable</span> trigger all;
</code></span></span>

disable trigger all 会禁用表上的所有外键，同时也禁用负责验证约束的内部触发器。

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em>#  INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(1) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
postgres=<span style="color:#5c6370"><em># alter table t_product disable trigger all;</em></span>
ALTER TABLE
postgres=<span style="color:#5c6370"><em>#  INSERT INTO t_product VALUES (1, 'PostgreSQL consulting', 1);</em></span>
INSERT 0 1
</code></span></span>

这一点在做数据迁移时也很重要。数据迁移的时，遇到有外键约束的表，如果不注意表数据的导入顺序将会导致数据加载失败。

怎么重新启用外键盘约束？

<span style="color:#000000"><span style="background-color:#ffffff"><code>alter table t_product <span style="color:#e6c07b">enable</span> trigger all;
</code></span></span>

看下有没有生效:

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># alter table t_product enable trigger all;</em></span>
ALTER TABLE
postgres=<span style="color:#5c6370"><em>#  INSERT INTO t_product VALUES (2, 'PostgreSQL consulting1', 2);</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(2) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
</code></span></span>

启用之后，可以发现，外键约束已经启用，它会对于后续新插入或者更新的数据会进行检查。

这里有个问题，怎么去验证老的数据呢？

方法：修改pg_constraint表，将convalidated置为false，然后使用 Alter table validate constraint语句。
示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code>postgres=<span style="color:#5c6370"><em># select convalidated from pg_constraint where conname = 't_product_currency_id_fkey';</em></span>
 convalidated
--------------
 t
(1 row)

postgres=<span style="color:#5c6370"><em># update pg_constraint set convalidated = false where conname = 't_product_currency_id_fkey';</em></span>
UPDATE 1
postgres=<span style="color:#5c6370"><em># select convalidated from pg_constraint where conname = 't_product_currency_id_fkey';</em></span>
 convalidated
--------------
 f
(1 row)

postgres=<span style="color:#5c6370"><em># Alter table t_product validate constraint t_product_currency_id_fkey;</em></span>
ERROR:  insert or update on table <span style="color:#98c379">"t_product"</span> violates foreign key constraint <span style="color:#98c379">"t_product_currency_id_fkey"</span>
DETAIL:  Key (currency_id)=(1) is not present <span style="color:#c678dd">in</span> table <span style="color:#98c379">"t_currency"</span>.
</code></span></span>

我们现在知道了对于存在外键约束的表，表数据的插入顺序很重要，对于这一点，cybertec提供了一个魔法SQL，用于查询我们应该插入数据的顺序。

<span style="color:#000000"><span style="background-color:#ffffff"><code>WITH RECURSIVE fkeys AS (
   /* <span style="color:#e6c07b">source</span> and target tables <span style="color:#c678dd">for</span> all foreign keys */
   SELECT conrelid AS <span style="color:#e6c07b">source</span>,
          confrelid AS target
   FROM pg_constraint
   WHERE contype = <span style="color:#98c379">'f'</span>
),
tables AS (
      (   /* all tables ... */
          SELECT oid AS table_name,
                 1 AS level,
                 ARRAY[oid] AS trail,
                 FALSE AS circular
          FROM pg_class
          WHERE relkind = <span style="color:#98c379">'r'</span>
            AND NOT relnamespace::regnamespace::text LIKE ANY
                    (ARRAY[<span style="color:#98c379">'pg_catalog'</span>, <span style="color:#98c379">'information_schema'</span>, <span style="color:#98c379">'pg_temp_%'</span>])
       EXCEPT
          /* ... except the ones that have a foreign key */
          SELECT <span style="color:#e6c07b">source</span>,
                 1,
                 ARRAY[ <span style="color:#e6c07b">source</span> ],
                 FALSE
          FROM fkeys
      )
   UNION ALL
      /* all tables with a foreign key pointing a table <span style="color:#c678dd">in</span> the working <span style="color:#e6c07b">set</span> */
      SELECT fkeys.source,
             tables.level + 1,
             tables.trail || fkeys.source,
             tables.trail @> ARRAY[fkeys.source]
      FROM fkeys
         JOIN tables ON tables.table_name = fkeys.target
      /*
       * Stop when a table appears <span style="color:#c678dd">in</span> the trail the third time.
       * This way, we get the table once with <span style="color:#98c379">"circular = TRUE"</span>.
       */
      WHERE cardinality(array_positions(tables.trail, fkeys.source)) < 2
),
ordered_tables AS (
   /* get the highest level per table */
   SELECT DISTINCT ON (table_name)
          table_name,
          level,
          circular
   FROM tables
   ORDER BY table_name, level DESC
)
SELECT table_name::regclass,
       level
FROM ordered_tables
WHERE NOT circular
ORDER BY level, table_name;
</code></span></span>

输出结果示例：

<span style="color:#000000"><span style="background-color:#ffffff"><code> table_name | level
------------+-------
 t_currency |     1
 t_product  |     2
(2 rows)
</code></span></span>

该篇已首发到公众号PostgreSQL运维技术，欢迎来踩~

悄悄放一张：

PostgreSQL运维技术 

 

参考文档

https://www.cybertec-postgresql.com/en/postgresql-foreign-keys-and-insertion-order-in-sql/ https://www.infoq.cn/article/kahutf7zlid0biyhadiq

OperationalError: foreign key mismatch - “RESTAURANT“ referencing “GEOGRAPHIC“解决方案
weixin_43178406的博客
07-11 1万+
本文主要介绍了OperationalError: foreign key mismatch - “RESTAURANT” referencing "GEOGRAPHIC"解决方案，希望能对学习python的同学们有所帮助。 文章目录 1. 问题描述 2. 解决方案
【postgresql 基础入门】表的约束(一）主键与外键，数据的实体完整性与参照完整性，外键引用数据被修改时的动作触发
一个追逐梦想的码农
03-20 4983
数据库的约束是一种规则，用于限制或规范数据库中的数据，确保数据的完整性和一致性。这些约束可以定义在表级别或列级别，处理机制是一致的。约束不占用任何数据库空间，而是存在于数据字典中，并在执行SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，它增强了数据的完整性。
【学习笔记】PostgreSQL之外键
聪明马的博客
11-03 3699
在 PostgreSQL 中，外键（Foreign Key）是一种数据库约束，用于维护表之间的关联关系。外键确保一个表的一列（子列）的值引用了另一个表（父表）的主键或唯一键列的值。这有助于维护表之间的一致性和完整性，以及执行引用完整性检查。
postgresql基础
bestwinner的专栏
04-23 203
a.删除键:b.禁用键DISABLE。
外键约束 foreign key constraint
jackleechina2011212的博客
05-29 390
如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。外键是用来实现参照完整性的。 比如说： create table t_group ( id int not null, name varchar(30), primary key (id) ); insert into t_group ...
postgresql----数据库表约束----FOREIGN KEY
weixin_33711641的博客
06-19 369
六、FOREIGN KEY ---- 外键约束 外键可以是单个字段，也可以是多个字段。所谓的外键约束就是引用字段必须在被引用字段中存在，除非引用字段部分为NULL或全部为NULL（由MATCH TYPE决定），否则INSERT或UPDATE时将返回失败，且被引用字段必须有唯一约束或是主键。 外键约束语法相对较复杂一点，创建外键的语法如下： ALTER TABLE tbl_foreign CO...
mysql外键约束innodb_MySQL/InnoDB的外键约束（Foreign Key Constraint）
weixin_42465297的博客
01-19 619
MySQL/InnoDB的外键约束(Foreign Key Constraint)SQL外键约束一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。FOREIGN KEY 约束用于预防破坏表之间连接的动作。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。SQL FOREIGN KEY Constraint on CREATE ...
PostgreSQL 中如何处理数据的并发插入和唯一约束的冲突解决？
技术笔记
07-15 1662
在 PostgreSQL 中，处理并发插入和唯一约束的冲突是一个重要的问题。我们可以使用唯一索引、语句、事务和锁等方法来解决这个问题。在实际应用中，我们应该根据具体的业务需求和场景，选择合适的解决方案。同时，我们还需要注意性能问题，避免不必要的锁竞争和资源浪费。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📚领书：PostgreSQL 入门到精通.pdf📙PostgreSQL 中文手册📘PostgreSQL 技术专栏🍅优快云社区-墨松科技。
【PostgreSQL】PostgreSQL 教程
No8g攻城狮的博客
07-17 550
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
PostgreSQL的学习心得和知识总结（一百四十八）|查看 PostgreSQL 17 中的新内置排序规则提供程序
孤傲小二~阿沐的博客
07-15 1357
查看 PostgreSQL 17 中的新内置排序规则提供程序
第11章 索引（postgresql v17）
chinusyan的专栏
10-18 1116
第11章 索引（postgresql v17）
PostgreSQL foreign key
weixin_33875839的博客
01-25 613
什么是外键？ 外键就是用来关联表的节点，是为了保持表的参照完整性...
【PostgreSQL】约束-外键
圈小圈DBA的博客
12-31 2642
CREATE TABLE 表名 (列名 数据类型,...外键列名 数据类型 REFERENCES 参考表名 (参考列名)
PostgreSQL外键全解析：从概念到实践的进阶指南
喵手的博客
11-27 1314
在数据库设计中，外键就像一位严格的“秩序管理员”，它不仅把各个表之间的关系管理得井井有条，还负责保障数据的完整性。PostgreSQL作为开源数据库中的佼佼者，自然也为我们提供了灵活强大的外键约束机制。但在实际开发中，外键处理起来却可能没那么简单，经常会遇到一些操作上的“坑”。所以今天我们就来深入探讨PostgreSQL中的外键，看看如何用好这个“秩序管理员”。📋希望这篇文章能让你在设计数据表时，不仅能够合理地使用外键，还能灵活地处理各种场景下的外键操作。让我们一探究竟吧！
PostgreSQL外键
GeekLee的博客
10-09 3131
回忆一下 Chapter 2 里的 weather 和 cities 表。考虑一下下面的问题：你想确保没有人可以在 weather 表里插入一条在 cities 表里没有匹配记录的数据行。 这就叫维护你的表的参考完整性。 在简单的数据库系统里，实现（如果也叫实现）这个特性的方法 通常是先看看 cities 表里是否有匹配的记录， 然后插入或者拒绝新的 weather 记录。 这个方法有许多问题，而且
MySQL学习笔记（7）——FOREIGN KEY外键约束
练涛
04-10 313
FOREIGN KEY外键约束 1. 父表和子表必须使用相同的储存引擎，而且禁止使用零食表。 2. 数据表的储存引擎只能为InnoDB。 3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。 4. 外键列回头参照列必须创建索引。如果外键列不存在索引的话MySQL将自动创建索引。 数据表的默认储存引擎需要在my.ini文件中修改：...
PostgreSQL 外键示例
Uncle.Cui的技术博客
07-19 343
create table cities ( city varchar(80) primary key, location point ); create table weather ( city varchar(80) references cities(city), temp_lo int, temp_hi int, prcp real, date date );
PG 禁用外键约束
岳麓丹枫
01-19 1914
构造数据 create table test(id int primary key , name varchar); insert into test values (1, 'a'); create table testb(id int , name varchar, constraint fk_id foreign key (id) references test(id) match simple on delete cascade ); insert INTO testb values(1,'a1.
外键和主键的Postgres和索引
xfxf996的博客
07-18 1317
Does Postgres automatically put indexes on Foreign Keys and Primary Keys? Postgres会自动将索引放在外键和主键上吗？
数据库设计包括哪些方面？除了范式之外，核心思想？
最新发布
06-04
<think>嗯，用户这次提出的问题很专业啊，他想了解数据库设计除了范式之外的核心思想和最佳实践。看来用户对数据库设计已经有了一定基础，不满足于教科书式的范式理论，想要更落地的实战经验。从用户提供的引用材料来看，他应该已经掌握了1NF到5NF的基本概念，特别是反复提到了多值依赖和反范式设计。不过用户特意强调“除了范式”，说明他可能在实际工作中遇到了范式无法解决的痛点，比如性能问题或者业务模型适配问题。用户身份推测可能是中级开发人员或DBA，正在参与实际数据库设计项目。他的深层需求可能是想平衡理论规范和工程实践，尤其是在高并发或复杂业务场景下的设计技巧。上次回答中我提到过反范式设计和一致性工程，他这次显然想深入这个方向。需要特别注意用户提到的“核心思想和主要方面”这两个关键词。核心思想应该聚焦数据库的本质目标：数据安全和高效访问；主要方面则需要覆盖全生命周期，从抽象建模到具体优化策略。关于“除了范式之外的最佳实践”，其实业内有很多经验性规则。比如在物联网场景常用“数据分桶”策略，电商系统的“冗余计数”方案，但这些可能过于具体。更适合用户的是跨行业通用的五大支柱：数据完整性、性能、安全、可扩展和维护成本。每个支柱下都可以展开典型方法，比如读写分离具体怎么降低主库压力。考虑到用户上次关注反范式，这次可以强化使用场景的对比分析。特别注意标注反范式的风险控制手段，比如触发器补偿机制，这样用户不会误以为反范式就是随便破坏规则。技术上还需要澄清一个概念误区：很多人认为非关系型数据库就不需要设计原则。其实文档型数据库同样有“索引优化”“数据分片”等核心思想，这点可以在“技术选型”部分点明。不过用户没明确问NoSQL，简单提及即可。最后示例部分非常关键。用户上次对学院案例有互动，这次延续教育系统场景能降低理解成本。选课系统的例子能同时展示范式理论的局限（效率问题）和反范式带来的提升，顺带引出查询优化的可视化解释计划。</think> 好的，数据库设计远不止范式理论，范式只是其“规范化”的一个核心方面。完整的数据库设计需要综合考虑多个维度和核心思想。以下是数据库设计的核心思想、主要方面以及超越范式的最佳实践： **核心思想:** 1. **准确性与完整性：** 核心目标是**准确、完整**地反映现实业务中的数据及其关系（即数据模型）。数据必须真实反映业务实体、状态和规则。 2. **易用性与效率：** 设计必须便于用户（应用程序和最终用户）**高效**地存储、检索、操作和管理数据。查询性能、更新速度是关键考量。 3. **数据一致性：** 确保数据在数据库中的**一致**状态。避免冗余导致的不一致（范式着力于此），并维持实体完整性、参照完整性等约束。 4. **可维护性与可扩展性：** 设计应易于理解、修改和适应未来的业务变化或数据量增长（**可伸缩性**）。结构清晰、文档齐全至关重要。 5. **安全性：** 保护数据免受未授权访问、修改或破坏，确保数据的保密性、完整性和可用性。 **数据库设计的主要方面：** 1. **需求分析：** * 深入了解业务领域、流程、规则和用户需求。 * 识别核心**实体（Entity）**（如：用户、订单、产品）及其关键**属性（Attribute）**。 * 确定实体之间的**关系（Relationship）**（一对一、一对多、多对多）以及**基数约束**。 * 收集业务规则（如：订单状态转换规则、库存不能为负等）。 * *核心思想：准确性、完整性、理解业务。* 2. **概念数据模型（CDM）：** * 使用ER图（实体-关系图）等工具可视化表示实体、属性和关系。 * 专注于业务需求，**独立于具体数据库技术**。 * *核心思想：抽象化业务需求、建立领域共识。* 3. **逻辑数据模型（LDM）：** * 将概念模型转化为适用于关系型数据库的结构（表）。 * **应用范式（Normalization）**：目标是减少冗余、消除异常（插入、更新、删除异常）。这是保证数据一致性和完整性基础的重要过程[^2][^3]。 * 定义： * 表（Table）对应实体或关系。 * 列（Column/Field）对应属性。 * 主键（Primary Key, PK）唯一标识行。 * 外键（Foreign Key, FK）表示表间关系。 * 定义数据类型、是否允许空值等约束。 * *核心思想：数据一致性、结构清晰、减少冗余、理论基础（范式）。* 4. **物理数据模型（PDM）：** * 针对具体的数据库管理系统（DBMS，如 MySQL, Oracle, PostgreSQL）实现逻辑模型。 * 进行**性能优化**考虑： * **反范式化（Denormalization）**：*有目的地引入数据冗余*以提升查询效率（如：在订单表中冗余客户姓名，避免每次都要联查客户表）[^1]。这是范式之外最关键的*实践权衡*。 * **索引（Indexing）**：创建合适的索引（聚簇索引、非聚簇索引、覆盖索引、全文索引等）加速搜索。索引设计是一门艺术。 * **分区（Partitioning）**：将大表按规则（范围、列表、哈希）拆分成物理文件，提升查询和管理效率。 * 数据类型选择（考虑存储空间和精度）。 * 存储引擎选择（如 InnoDB vs MyISAM in MySQL）。 * 定义表空间、文件组等物理存储结构。 * *核心思想：性能、效率、技术适配、物理存储、工程权衡（反范式化！）。* 5. **完整性约束定义：** * **实体完整性：** 主键值唯一且非空。 * **参照完整性：** 外键值必须存在于父表的主键中，或为空（根据业务规则）。 * **域完整性：** 列的数据类型、格式、范围（CHECK约束）或是否允许空值（NOT NULL）。 * **业务规则约束：** 使用存储过程、触发器或应用逻辑实现更复杂的业务规则。 * *核心思想：数据一致性、准确性、业务规则强制。* 6. **安全设计：** * 用户角色与权限管理（如：GRANT, REVOKE，基于角色的访问控制 RBAC）。 * 数据加密（传输加密 SSL/TLS，静态加密 TDE）。 * 审计（Audit Trail）：记录关键操作。 * *核心思想：数据安全、访问控制、合规性。* **超越范式的最佳实践：** 1. **适度反范式化（关键！）：** 这是平衡范式与性能的核心手段。 * **常见场景：** 频繁执行的复杂查询（如包含多个JOIN的报告）、读密集型应用、需要极低查询延迟的场景。 * **常见手法：** 预先计算聚合值（计数、总和、平均值存入表）、冗余常用查询需要的列、创建包含多表数据的物化视图。 * **重要原则：** **理解并明确引入冗余的目的和代价**。冗余带来性能提升的同时，增加了更新复杂度和存储成本，必须通过应用逻辑或触发器维护冗余数据的一致性。 2. **以查询为中心的设计：** 分析系统核心业务流程和高频查询语句，优化相关表的关联方式和索引策略。 3. **合理选择主键：** * 优先使用**代理键（Surrogate Key）** （如：自增整数 ID，UUID），而非自然键（如身份证号、产品编码）。代理键短小、稳定、无业务含义，性能更好，不易因业务变化而改变。 * 谨慎使用复合主键，会增加外键的复杂度。 4. **一致性工程：** * **原子性（Atomicity）：** 使用事务（Transaction）确保操作要么全成功要么全失败。 * **隔离性（Isolation）：** 理解不同事务隔离级别（Read Uncommitted, Read Committed, Repeatable Read, Serializable）对性能和一致性的影响。 * 最终一致性策略：在分布式数据库或强调高可用性的场景，可能需要接受最终一致性。 5. **文档化：** * 详细记录数据字典（表、列、含义、类型、约束）。 * 记录ER图、重要的设计决策（如反范式选择原因）、业务规则、核心查询。 * 记录权限设置和变更历史。 * *核心思想：可维护性、团队协作。* 6. **考虑可扩展性：** * 垂直扩展（升级服务器） vs 水平扩展（分库分表 Sharding）：设计时就需有所考虑。 * 分片策略（如何拆分数据）对应用透明性设计的影响很大。 7. **技术选型：** * 关系型数据库（RDBMS） vs 非关系型数据库（NoSQL）？根据数据结构（结构化/半结构化/非结构化）、查询模式（点查/复杂联查/聚合）、一致性要求、扩展性需求来选择。**没有银弹**。核心思想：**合适胜于单一理论**。范式主要针对 RDBMS。 **总结核心思想：** 数据库设计是在 **准确完整建模业务数据、确保强一致性** （范式的重要贡献）与 **追求极致性能和可扩展性** （反范式化、索引、分区等工程实践的舞台）之间**进行精心权衡的艺术**。一个“好”的设计必须在这些冲突目标中找到一个平衡点，并充分考虑安全性、可维护性和未来的演化成本。范式是基础规范，而高效的设计需要在理解范式原理的基础上，根据实际场景，明智地运用和**突破**这些规范来实现综合目标[^1][^2][^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值