关系数据仓库中的引用完整性。值得吗？有哪些替代方案？
如果您必须使用 SQL Server 2008 构建符合圣经标准的关系数据仓库，您会使用外键来强制数据完整性，还是会使用其他方法？
我喜欢外键，因为您只需将它们正确一次，并且它们始终存在以保护完整性。我正在考虑走禁用、加载、启用路线。
有什么想法吗？
提前致谢。
If you had to build a relational data warehouse of biblical proportions using SQL Server 2008, would you use foreign keys to enforce data integrity, or would you use some other means?
I like foreign keys because you only need to get them right once, and they are always there to protect integrity. I was thinking of going the disable, load, enable route.
Any thoughts?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
首先，我不会构建一个（物理上）符合关系模式的数据仓库。所提议的数据仓库是完全规范化的，还是问题中的“关系”一词只是表明它将建立在 SQL 数据库中？
In the first place, I wouldn't build a data warehouse that (physically) conformed to a relational schema. Is the proposed data warehouse to be fully normalised, or does the word "relational" in the question simply indicate that it will be built in a SQL database?
哦，我当然会的！您必须记住的是，您的数据库是您的数据存储，而不仅仅是前端的数据存储。这是微妙的区别，但当您开始考虑未来时，这一点很重要。现在，您（大概）拥有管理应用程序，但谁能说将来情况仍然如此？
通过将尽可能多的验证卸载到数据库上，您可以在某种程度上证明您的应用程序的未来性 - 至少如果其他人尝试针对您的数据库进行开发，您的更多假设将成立。
在数据库端这样做的缺点是插入速度较慢，因此您需要权衡应用程序对读取和写入的负担有多大。在工作中，我们对读的需求比对写的需求要多得多，因此引用完整性似乎是显而易见的。然而，我们的表很大（并且可以免费导入），因此我们采用了多步骤导入路线：创建表、插入数据、创建索引，然后创建外键和其他约束。
我希望这有帮助！
Oh, I most certainly would! What you have to remember is your database is your data store, it is not just the data store for the frontend. This is subtle difference, but it's important when you start to consider the future. Right now, you (presumably) own the management applications, but who is to say that in the future this will still be the case?
By off loading as much validation as you can onto the database, you somewhat future proof your application - at least if someone else tries to develop against your database, more of your assumptions will hold.
The drawbacks of having this on the database side is slower insertion, so you need to weigh up how heavy your application is against reading and writing. At work, we have much more demand for read than write, so referential integrity seems obvious. However, our tables are big (and freely available for importing), so we go down a multiple step import route of creating the tables, inserting the data, creating indexes, and then creating foreign keys and other constraints.
I hope this helps!
是的，我通常会使用外键。这对于任何数据库都很重要，但如果仓库是一个包含许多表的复杂数据库，则尤其如此。
在仓库中使用完整性约束的原因与在任何其他数据库中使用完整性约束的原因非常相似：它最大限度地减少了错误数据进入数据库的风险；它通常是实施此类完整性规则的最经济、最高效的方法；这意味着优化器可以使用这些约束来提高查询性能；这些约束也可供使用数据并需要解释其结构的开发工具和用户使用。
Yes I would generally use foreign keys. That's important in any database but perhaps especially so if the warehouse is a complex one with many tables.
The reasons for using integrity constraints in the warehouse are much the same as in any other database: It minimises the risk of incorrect data getting into the database; It is very often the most economical and performant method of implementing such integrity rules; It means those constraints are available to the optimiser to improve query peformance; The constraints are also available to development tools and users who consume the data and need to interpret its structure.