使用外键约束从 SQL 脚本恢复数据库

Question

我正在尝试使用 SQL 脚本恢复数据库，但是外键约束妨碍了

我获取 MySQL 数据库并将其转移到 PostgreSQL。 由于 MySQL 创建表语法最终非常不同，因此我采用了另一个具有相同架构但数据不同的 PostgreSQL 数据库，并仅从中恢复了架构。 换句话说，我现在有一个包含表、约束、序列和所有这些内容的数据库，但里面没有数据。

所以，是时候恢复数据了。 我使用 phpMyAdmin（仅数据）作为 SQL 脚本备份 MySQL DB（pgAdmin 由于某种原因似乎不接受 zip 或 gzip 文件）并运行 SQL 脚本。 现在，这就是问题开始发生的地方，这是很自然的，我将从 MySQL 转到 PostgreSQL，所以语法错误肯定会发生。

但是，还有其他与语法无关的问题，例如：

ERROR: insert or update on table "_account" violates foreign key constraint "fk_1_account"
DETAIL:  Key (accountid)=(2) is not present in table "_entity".

所以，是的，基本上，存在外部约束，查询正在尝试将数据插入到 _account 表中，但相应的数据已尚未插入到 _entity 表中。 我该如何解决这个问题？有没有办法让 pgAdmin3/PostgreSQL 禁用所有约束，插入数据，然后重新启用约束？

我遇到的一个与语法相关的错误是这样的：

 INSERT INTO _accounttype_seq (id) VALUES (11);

该语句的 PostgreSQL 等效项（如果我是正确的）是

 ALTER SEQUENCE _accounttype_seq INCREMENT BY 11; 

但是，运行整个脚本并更改所有 200 多个序列插入语句有点痛苦。所以，我在这里很懒，但是还有更简单的方法来处理序列吗？

或者，你们对一套不同的工具有什么建议，可以让这件事变得更容易吗？

感谢您抽出时间，祝您有美好的一天。

Answer 1

不要试图绕过外键约束。这是确保数据不良的方法。

首先查看约束并确保以正确的顺序插入到表中。如果 _entity 是“_account”的父级，那么应该首先填充它。

接下来，您需要让脚本将任何失败的记录移动到异常表中。然后您可以查看它们并了解数据完整性问题是什么，以及是否需要永久丢弃记录或尝试找出丢失的父值应该是什么，如果它是关键数据，例如客户不再存在的订单（可能在任何开始时没有正确的 fks 的系统中）。必须保留记录并且无法确定什么父值应该是，您可以在客户表中创建“未知”记录并将所有不良订单分配给该客户 ID。

手动更改更改序列即使很无聊，也不会花很长时间。在这种类型的转换中，您还需要手动处理很多其他事情。

我会尝试为 PostgreSQL 找到一个数据导入工具 - 我生活在 SQL Server 世界中，我会使用 SSIS，但在 PostgreSQL 世界中你需要相当于 SSIS 的工具。

Answer 2

显然，外键实际上并未在 MySQL 中强制执行（可能是因为使用 MyISAM），或者生成的 SQL 只是以错误的顺序执行。

如果“只是”顺序错误，我看到两种可能的解决方案：

1. 编辑生成的脚本，或者将所有 FK 定义移动到脚本末尾
2. 编辑每个 FK 约束的定义并将它们全部设置为初始延迟.然后将脚本作为单个事务运行，最后仅进行提交。

编辑（因为这太多了，无法作为注释）

仅当使用选项创建约束时，使用SET CONSTRAINTS ALL DEFERRED才有效>可延期。

要在一个事务中运行所有内容，您必须确保已关闭自动提交。然后只需运行INSERT并在最后发出COMMIT。仅当您启用了自动提交时， ; 才会提交。

如果您想独立于自动提交设置，请使用 [BEGIN][1] 启动脚本，并确保最后只有一个 COMMIT。

BEGIN DEFERRABLE
   INSERT INTO table_one ... ;
   INSERT INTO table_two ... ; 
   .....
COMMIT;