将外键构建到遗留数据库中的技巧
我有一个没有任何外键的数据库。我做了一些检查，发现有相当多的孤立记录。
它是一个相当大的数据库，有 500 多个表，我正在考虑重新构建外键的可能性。
除了随着时间的推移对每个表进行拖网之外？
有人曾经经历过这个过程吗？也许可以提供一些关于如何使这个过程变得更容易的见解或技巧。
任何帮助建议表示赞赏。
I've got a database that doesn't have any foreign keys. I've done some checks and there are a a fair few orphaned records.
Its a pretty large database 500 + tables and I'm looking at the possibility of building the foreign keys back in.
Other than trawling though every single table over time?
Has anybody ever been through this process before and can maybe offer some insights or tips on how to make the process a little easier.
Any help advice appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我假设你的意思是“没有任何外键约束”......如果没有外键，你根本不知道哪些记录匹配。
主键字段和外键字段是否具有相同的名称？例如，PK 表有一个“CustomerId”字段，FK 表也有一个“CustomerId”字段？如果是这样，您也许能够查询列属性（也许使用 INFORMATION_SCHEMA，您没有提到 RDBMS）来找出一些隐含的关系。只需查询所有具有名为“CustomerId”的字段（该字段不是 PK）的表，并且有一个很好的（但不确定）赌注，这些表应该对 Customer 表具有 FK 约束。您甚至可以使用查询的输出来生成 DDL 来创建约束。
I assume you mean "doesn't have any foreign key constraints"...if there were no foreign keys, you wouldn't know which records matched at all.
Do the primary and foreign key fields have the same name? As in, the PK table has a "CustomerId" field and the FK table(s) also have a "CustomerId" field? If so, you might be able to query the column properties (perhaps using INFORMATION_SCHEMA, you didn't mention an RDBMS) to figure out some implied relationships. Just query for all the tables that have a field called "CustomerId" that is not a PK and there's a good (but not certain) bet that those tables should have an FK constraint to the Customer table. You could even use the output of the query to generate the DDL to create the constraints.
您可以从最大到最小的表进行工作，或者从数据库性能最低的区域开始。添加键应该会显着提高您的性能，但您必须首先解决孤立行。为此，您可能需要企业的意见。预计他们会对正在发生的事情感到非常困惑。
You can work from the largest to smallest tables, or start with the least performant area of the database. Adding keys should help your performance significantly, but you'll have to resolve the orphan rows first. You may need input from the business for that. Expect them to be very confused about what's going on.