主页 / dba / 问题 / 74457
Accepted
Agi Hammerthief
Asked: 2014-08-21 07:15:03 +0800 CST

我如何证明删除外键的行为不会破坏现有数据？

  • 772

TL;DR：证明在实践中，alter table table_name drop foreign key constraint_name语句的执行不会破坏现有数据。重要的考虑是语句本身的执行；考虑事实后的数据更改无关紧要。（以此类推：打开马厩的门是有害的行为，而不是奔马。）

我的老板和我对是否在 MySQL/InnoDB 数据库中使用外键有不同意见。我支持使用它们来执行 RI 并利用ON DELETE CASCADEand ON UPDATE RESTRICT/ SET NULL，而他反对（相信他可以在应用程序级别执行 RI）。

他的论点主要是：

  1. Drupal 不使用它们并且没有它们也能很好地相处，那么我们为什么要这样做呢？
  2. 当您需要更改数据和/或结构时，它们太不灵活了。
  3. 他已将它们从现有表中删除以进行更改，这导致数据损坏仅在数周或数月后才会在高流量/高活动站点上引起注意，因此他宁愿不使用它们。

我的论点主要是：

  1. 并非 Drupal 5-7 (MyISAM, SQLite 3) 支持的所有数据库/数据库引擎都默认强制执行FK，因此 Drupal 仅将它们保留为文档，而在 D8 中可能会有所不同。
  2. 是的，处理它们可能会很痛苦，但问题可能在于开发人员的规划/设计不佳，而不是 FK。
  3. 当然，在 DBMS 级别不使用 FK 执行 RI 比其他方式更有可能导致数据损坏。（例如，当现有内容引用必须具有特定状态的用户时，D6 的用户引用模块不限制更改用户状态）。
  4. 让代码做/尝试 DBMS 已经做的事情是浪费时间和资源。他怎么能保证他的代码和 DBMS 一样好（或更好）？

本质上，如果我能证明删除外键本身的行为（不管后续数据插入/更新）不会破坏现有数据，他就会接受我的想法。我无法清楚地证明它们的有用性/优势，因为除了将执行前的数据与执行后的数据进行比较之外，我不确定如何令人满意地观察/记录 adrop foreign key在执行后立即产生的效果。

那么，我如何说服我的老板我们应该使用外键，证明以后删除/更改它们的行为不会破坏现有数据，而使用它们不会引起任何问题？我将如何最好地设置实际使用测试？

注意：我并不是很想证明我是正确的（从自负的角度来看）使用 FK，但是在 DBMS 级别使用它们比在应用程序代码中更有益于数据和应用程序代码等级。

mysql database-design

5 个回答

  1. 在应用程序级别实现这些东西是一场噩梦。您和您的团队将不得不测试、仔细检查和重新测试代码，这些代码与 MySQL（针对 InnoDB）在YEARS期间为数百万用户所做的事情完全相同。

    关注这里的讨论（我在 stackoverflow 上看到的最好的线程之一）。尊重您和您的团队，您的老板真的认为您可以在不到 5 年的时间内编写出具有重要功能的无错误 RI（参照完整性）代码吗？我当然不会！

    我无法告诉你我在其他论坛（主要是 Oracle）上读到多少次，其中一些可怜的 schmuck 对他继承的应用程序大哭，在应用程序层强制实施 RI。孤儿记录、没有孩子的父母、不一致的数据……不胜枚举……数据也可能是瑞士奶酪，它有那么多漏洞！告诉你的老板读一读数据库专业人士的这本书（Oracle 专家，我见过的一些最好的、最易读的技术著作）。从这里的评论- 乔纳森刘易斯（一个写了一本 530 页的书的人）的概述（得到这个）甲骨文优化器的基本原理。

    第 10 章：设计灾难，乔纳森·刘易斯 (Jonathan Lewis)

    更多战争故事，献给第 8 章的粉丝！“现在准备好阅读有关‘世界上最糟糕的甲骨文项目’的所有内容。”——乔纳森·刘易斯。

    本章描述了开发 Oracle 数据库应用程序中一些最常见的错误。你肯定会认出其中一些，因为有很多人固执地坚持某些信念。我知道当我遇到以下常见论点时，我喜欢提出他的几个观点：
    1. 我们希望我们的应用程序是“数据库独立的”。
    2. 我们将在应用程序级别检查数据完整性，而不是利用 Oracle 的约束检查能力。
    3. 我们想为我们的主键使用序列。

    注意第2点！如果你的老板坚持这种疯狂，那么我给你的建议是跑得快，跑得远！您将在不断救火的悲惨地狱中度过您的日子，永远无法发挥您作为开发人员或 DBA 的潜力，而且您学到的东西很少！只是一些想法！

    • 11
  2. Best Answer

    直接反驳观点：

    Drupal 不使用它们并且没有它们也能很好地相处，那么我们为什么要这样做呢？

    Drupal 支持许多数据库层，也许其中至少有一个不支持 FK，他们选择坚持使用最低的通用特性集？很多人确实使用它们，人们不使用它们的一个数据点相对没有意义。我知道有人在车上不系安全带，但我和其他大多数人一样...

    当您需要更改数据和/或结构时，它们太不灵活了。

    如果您将结构更改到需要更改 FK 关系的程度，那么现有设计中的任何内容都可能看起来不灵活（特别是如果现有设计与您开始建模的系统不匹配，这可能就是您为什么正在寻求改变现有结构）。

    在更改数据时，它们故意不灵活。它们旨在不允许您以可能损害完整性的方式更改数据，即使是暂时的。询问外键约束将停止数据修改的示例，并且对于每个示例，我们将能够解释为什么这是一件好事以及如何使用约束来实现您的目标而不破坏它（或者相反，我们可能会而是告诉您为什么特定的 FK 关系是错误的 - 但这不会使其他 FK 错误，它只是意味着您的模型的那部分存在问题）。

    他已将它们从现有表中删除以进行更改，这导致数据损坏仅在数周或数月后才会在高流量/高活动站点上引起注意，因此他宁愿不使用它们。

    删除它们不会导致腐败。删除它们允许其他一些代码中的错误随着时间的推移导致损坏，并且没有被注意到。如果密钥已经到位，则该过程会引发错误，并且可能因此会注意到并修复它的问题，而不是静默允许破坏更多数据直到注意到。删除约束所做的所有事情都是停止数据库对未来的插入/更新/删除执行约束——它不会影响现有数据。

    我不确定您将如何最终向一个确定情况并非如此的人证明。

    我能想到的唯一证据是第一原则：重申外键约束实际上做了什么，从而表明它们不会允许出现不一致，如果有这个人想到的腐败的具体例子，你可以尝试创造密钥到位的问题（表明密钥会导致错误而不是允许创建不一致）。

    更具体地说，关于删除约束的直接影响：表明删除它们根本不会改变现有数据。创建数据库的副本，删除副本中的键，并运行数据的完整比较以显示由于解除约束而没有发生任何变化。

    他反对（相信他可以在应用程序级别执行 RI）。

    充其量这是对开发/测试时间的错误利用，最坏的情况是它会为以后制造一场噩梦。您正在重新发明轮子，可能效率低下并且可能存在错误，您必须在代码现在和未来每个应用程序中触及该数据的任何地方实现您的新轮子，而不是每次都简单地让数据库处理它，如果其中一个位代码有一个错误，数据可能永远失去所有应用程序的完整性。

    当然，DBMS 无法为您强制执行复杂的业务规则，因此您必须在您的 BL 层中实现它们，但是对于这样的基础知识，让 DB 来处理它。

    一个没有提到但我听过好几次的额外的：当我们对父对象进行更改时，它们效率低下

    这是因为大多数 DBMS 不会为每个 FK 自动创建索引，许多人认为他们会这样做，并且对他们没有看到他们期望的性能指标感到惊讶。数据库不这样做，因为在不需要时可能会非常浪费（这比您预期的更常见）。如果您需要在 FK 的列上建立索引，以使级联更新/删除（或仅在该方向上的简单连接查询）高效，请创建一个。

    • 10

  3. 删除外键不会损坏数据，因为您正在对索引进行 DDL。

    一旦你这样做了，未来的数据完整性（即使是现有数据）也需要对其完整性进行测试。

    例子

    create table parent
(
    id int not null auto_increment,
    ...
    primary key (id)
);
create table child
(
    id int not null auto_increment,
    fk_id int not null,
    ...
    primary key (id),
    key fk_index (fk_id),
    CONSTRAINT FK FOREIGN KEY (fk_id) REFERENCES parent(id)
);

    假设您删除了约束

    create table parent
(
    id int not null auto_increment,
    ...
    primary key (id)
);
create table child
(
    id int not null auto_increment,
    fk_id int not null,
    ...
    primary key (id),
    key fk_index (fk_id)
);

    运行几天后，您应该运行以下查询

    孤儿记录

    select COUNT(A.id) orphans from child A left join parent B
on A.fk_id = B.id where B.id IS NULL;

    如果你得到一个非零数字orphans，你就会得到孤立的子记录。删除约束可能是一个可能的因素。

    没有孩子的父母

    select COUNT(A.id) childless_parents from parent A left join child B
on A.id = B.fk_id where B.id IS NULL;

    如果你得到一个非零数childless_parents，你的父母没有孩子（对不起，这听起来相当多余）。删除约束可能是一个可能的因素。

    结语

    在一个完美的世界中，参照完整性要么是事后的想法，要么是理所当然的。因此，如果你要删除外键约束，请醒来，把沙人从你的眼睛里拿出来，去你的数据库中找到所有的约束，并定期检查它们。

    在非零的情况下orphans，您必须删除这些记录或将它们分配给父母。

    • 2

  4. 其实我猜答案是你老板的话：

    3.他将它们从现有表中删除以进行更改，这导致数据损坏仅在几周或几个月后才在高流量/高活动站点上引起注意，因此他宁愿不使用它们。

    FK 删除不会更改数据，但是，如果有人像您的老板那样对 DB 运行查询，那么应用程序级别的 RI 如何防止损坏？没有什么可讨论的，但请记住，他必须删除 FK 才能更改数据，并且可能以违反被删除的 FK 实施的规则的方式。

    • 1

  5. TL;DR -- 解释 db 的工作原理

    长版：您不能证明删除 FK不会导致损坏，因为这实际上不是真的——理论上它可以。您可以展示的是，它不会导致腐败，并解释为什么它导致腐败的可能性比实际更理论上。

    我首先要解释为什么它实际上导致腐败的可能性实际上是微不足道的。外键是元数据，作为数据库引擎的程序用来决定如何处理传入命令的数据。 它不是您数据的一部分，但其处理方式基本相同。在删除有关外键的数据时导致损坏的数据库引擎几乎肯定会在删除数据时遇到相同的问题（即，从表 [a] 中删除数据会损坏或从表 [b] 中删除数据，并且您的数据更大，这会更频繁地发生）。

    数据的每一行都没有说“这是从 a 到 b 的 fk”，而是有一个单独的表，它引用了表 a 和表 b，以及相关的列。因此，表示外键的数据不会与您的数据混在一起，而是分开的——这就是为什么您可以拥有一个包含数百万甚至数十亿行的表，并且仍然在几微秒内删除外键约束. 您实际上可以使用该事实来证明没有发生损坏：创建一个包含大量行的示例数据库，创建一个 fk，备份数据文件，删除 Fk，将生成的数据文件与原始文件进行比较。如果数据 FK 关系嵌入在您的数据中，则所有这些都必须更改，但事实并非如此，这就是它可以如此快速的原因。它' s 也是为什么当你添加或删除一个 fk 约束时你的数据库的大小基本不变的原因。与索引不同，约束仅作为规则存在，没有任何重要大小的实际数据与之关联。

    • 1

相关问题