外键与完整性检查约束 [英] Foreign key vs check constraint for integrity

问题描述

我正在构建一个系统，它是一个中央存储库，用于存储来自许多其他系统的数据.当其他系统数据更新时，需要同步过程来更新中央存储库.将有一个 sync_action 表来标识中央存储库需要与哪个系统同步以及所需的同步类型.有一组不太可能改变的已定义操作.下面是一个精简的系统.

I am building a system that is a central repository for storing data from a number of other systems. A sync process is required to update the central repository when the other systems data is updated. There will be a sync_action table to identify which system the central repo needs to sync with and the type of sync required. There are set of defined actions that is very unlikely to change. A slimmed down system is below.

在我看来，我可以通过两种方式来解决这个问题:

As I see it I can approach this in two ways:

选项 1) 有一个 Action 表，其中包含 3 个可用的操作.有一个 sync_action 表，它使用外键来引用所需的操作.

Option 1) Have an Action table that has the 3 actions available. Have a sync_action table which uses a foreign key to reference the actions required.

表格:系统

ID Description
 1 Slave System 1
 2 Slave System 2

表格:动作

ID  Description
 1  Insert
 2  Update
 3  Delete

表格:Sync_action

ID  Action  System
 1     1       1
 2     2       1

选项 2) 在 sync_action.action 列上使用检查约束代替外键，因此只有操作 Insert/Update/Delete 可以插入.

Option 2) Instead of a foreign key use a check constraint on the sync_action.action column so only the actions Insert/Update/Delete can be inserted.

表格:Sync_action

ID  Action  System
1   Insert    1
2   Update    1

我想知道在完整性约束、外键与检查约束之间做出决定时，哪些因素会影响确定哪种方法更好.有类似的线程，但我没有发现它们足够明确.这可能是因为它取决于解释，但任何想法将不胜感激.

I would like to know what factors go into determining which is a better approach when deciding between integrity constraints, foreign key vs check constraint. There have been similar threads but I didn't find them definitive enough. This may be because its up to interpretation but any thoughts would be appreciated.

干杯

推荐答案

评论员们似乎一致同意:

The commentators seems to umanimously agree:

对(或多或少是静态的)引用表有一个 FOREIGN KEY 约束通常会更好.原因:

It's generally better to have a FOREIGN KEY constraint to a (more or less static) reference table. Reasons:

• 约束很容易扩展".要添加或删除选项，您只需在引用表中添加或删除一行.您不必删除约束并重新创建它.更重要的是，如果您在其他表的类似列中也有相同的约束.

• The constraint is easily "extendable". To add or remove an option, you only have to add or remove a row from the refernce table. You don't have to drop the constraint and recreate it. Even more, if you have same constraint in similar columns in other tables, too.

您可以附加额外信息(更多列)，如果需要，应用程序可以读取这些信息.

You can have extra information attached (more columns), that can be read by the applications if needed.

ORM 可以更好地处理(阅读:注意)这些约束.他们只需要读取表格，而不是元数据.

ORMs can deal better with (Read: be aware of) these constraints. They just have to read a table, not the meta-data.

如果您想更改操作代码，级联效果将处理其他(可能很多)表中的更改.无需编写 UPDATE 查询.

If you want to change the Action codes, the cascading effects will take care of the changes in other (possibly many) tables. No need to write UPDATE queries.

一个特定的 DBMS 还没有实现 CHECK 约束(耻辱)，尽管它确实有 FK 约束.

One particular DBMS has not yet implemented CHECK constraints (shame), although it does have FK ones.

正如@pst 提到的(我非常喜欢这种方法)，您可以使用合理的代码而不是代理整数 ID.所以，你的表可能是:

As @pst mentioned (and I prefer this approach very much), you can use a sensible code instead of a surrogate integer ID. So, your table could be:

表格:系统

SystemID Description
 1        Slave System 1
 2        Slave System 2

表格:动作

ActionCode Description
 I          Insert
 U          Update
 D          Delete

表格:SyncAction

ID  ActionCode  SystemID
 1     I          1
 2     U          1

这篇关于外键与完整性检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！