SQL Server外键约束的好处 [英] SQL Server Foreign Key constraint benefits

问题描述

我们正在设计一个数据库，我需要考虑一些FK（外键）
约束。但不限于
的正式结构化和正常化。
只有当它提供任何
的性能或可扩展性的好处的时候，我们才会去做。

我一直在通过一些有趣的文章和Google好处。以下是一些链接：

http ：//www.mssqltips.com/tip.asp？tip = 1296

我想知道更多关于FK的好处（除了正式结构化和着名的级联delete \update）。

• 默认情况下，FK不是索引的，所以考虑什么索引一个FK？

除了索引之外，这是否有助于优化SQL-Server中的查询执行计划？ >我知道还有更多，但我更喜欢专家在这方面发言。请指导我。


解决方案



• 外键不提供性能或可伸缩性的好处。 b $ b
• 外键强制执行参照完整性。如果有人试图从父表中错误地删除行，这可以提供一个实际的好处。


• 默认情况下，外键不会被编入索引。您应该索引外键列，因为这样可以避免在删除/更新父行时在子表上进行表扫描。


• 可以使外键列为空，并插入null。





We're designing a database in which I need to consider some FK(foreign key) constraints. But it is not limited to formal structuring and normalization. We go for it only if it provides any performance or scalability benefits.

I've been going thru some interesting articles and googling for practical benefits. Here are some links:

http://www.mssqltips.com/tip.asp?tip=1296

I wanted to know more about the benefits of FK (apart from the formal structuring and the famous cascaded delete\update).

• FK are not 'indexed' by default so what are the considerations while indexing an FK?

• How to handle nullable fields which are mapped as foreign key - is this allowed?

• Apart from indexing, does this help in optimizing query-execution plans in SQL-Server?

I know there's more but I'd prefer experts speaking on this. Please guide me.

解决方案

• Foreign keys provide no performance or scalability benefits.
• Foreign keys enforce referential integrity. This can provide a practical benefit by raising an error if someone attempted to delete rows from the parent table in error.
• Foreign keys are not indexed by default. You should index your foreign keys columns, as this avoids a table scan on the child table when you delete/update your parent row.
• You can make a foreign key column nullable and insert null.

这篇关于SQL Server外键约束的好处的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！