外键vs检查约束的完整性
我正在建立一个系统，它是用于存储来自多个其他系统的数据的中央存储库。当其他系统数据更新时，需要同步过程来更新中央存储库。将会有一个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:
strong>选项1 ）拥有一个具有3个操作的 Action 表。有一个使用外键引用所需操作的 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
表格： Action
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.
干杯
评论者似乎非常同意：
通常最好有一个 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 约束。
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
Table: SyncAction
ID ActionCode SystemID
1 I 1
2 U 1