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
The commentators seems to umanimously agree:
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.
ORMs can deal better with (Read: be aware of) these constraints. They just have to read a table, not the meta-data.
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.
One particular DBMS has not yet implemented CHECK constraints (shame), although it does have FK ones.
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:
Table: System
SystemID Description
1 Slave System 1
2 Slave System 2
在一个稍微独立的问题上，正如 @pst 提到的，我看到你已经被代理键怪物吃掉了。虽然这可以提高性能，但在您设想的大小的表（3个值，插入/更新/删除）甚至更大的表中，它可以提供所有服务to do 模糊了你想要实现的目标。
查看
ID Action System
1 1 1
2 2 1
并了解正在发生的事情并不容易，但是：
ID Action System
1 insert 1
2 update 1
更容易阅读；您可能还想考虑对 system 列执行相同的操作 - 我可能会这样做，尽管可能值的数量在此略有跳跃。只是我个人对这件事的想法...
I think you're confusing the difference between a foreign key constraint and a check constraint.
A foreign key constraint is there to enforce referential integrity and a check constraint constrains a column to containing only valid data. In your case this may seem like a minor difference but if we abstract it slightly I hope to make it clearer.
If we consider a table users with the columns user_id, user_name, address_id, join_date, active, last_active_month; I recognise that this is not necessarily the best way of doing things but it'll serve for the point I'm trying to make.
In this case it's patently ridiculous to have address_id as a constraint. This column could have any number of values. However, active, assuming we want a boolean y/n can only have two possible values and last_active_month can only have 12 possible values. In both these cases it's completely ridiculous to have a foreign key. There are only a certain number of values and by the definition of the data you are including these values cannot change.
In your case, while you could go for a check constraint, unless you can be absolutely certain that the number of actions will never change a foreign key is the correct way to go.
On a slightly separate matter, and as @pst mentioned, I see you've been eaten by the surrogate key monster. While this can result in performance improvements, in a table of the size you're envisaging ( 3 values, insert / update / delete ) or even a larger one all it serves to do is obscure what you're trying to achieve.
It's not easy to look at
ID Action System
1 1 1
2 2 1
and see what's going on, but:
ID Action System
1 insert 1
2 update 1
is far easier to read; you may also want to consider doing the same for the system column - I probably would, though the number of possible values jumps slightly in this. Just my personal thoughts on the matter...
发布评论
评论（2）
评论者似乎一致同意：
通常最好对（或多或少静态的）引用表设置
FOREIGN KEY约束。原因：该约束很容易“扩展”。要添加或删除选项，您只需在引用表中添加或删除一行。您不必删除约束并重新创建它。更重要的是，如果您在其他表中的类似列中也有相同的约束。
您可以附加额外的信息（更多列），如果需要，应用程序可以读取这些信息。
ORM 可以更好地处理（阅读：注意）这些约束。他们只需要读取表，而不是元数据。
如果您想更改操作代码，级联效果将处理其他（可能是许多）表中的更改。无需编写 UPDATE 查询。
一个特定的 DBMS 尚未实现
CHECK约束（遗憾），尽管它确实有 FK 约束。正如 @pst 提到的（我非常喜欢这种方法），您可以使用合理的代码而不是代理整数 ID。因此，您的表格可能是：
表：系统
表：操作
表：SyncAction
The commentators seems to umanimously agree:
It's generally better to have a
FOREIGN KEYconstraint 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.
ORMs can deal better with (Read: be aware of) these constraints. They just have to read a table, not the meta-data.
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.
One particular DBMS has not yet implemented
CHECKconstraints (shame), although it does have FK ones.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:
Table: System
Table: Action
Table: SyncAction
我认为您混淆了外键约束和检查约束之间的区别。
外键约束用于强制引用完整性，检查约束限制列仅包含有效数据。在你的情况下，这似乎是一个微小的差异，但如果我们稍微抽象一下，我希望使它更清楚。
如果我们考虑一个表
users，其中包含列user_id、user_name、address_id、join_date、active、last_active_month；我认识到这不一定是最好的做事方式，但它有助于我想要表达的观点。在这种情况下，将
address_id作为约束显然是荒谬的。该列可以有任意数量的值。但是，active假设我们想要一个布尔值y/n只能有两个可能的值，而last_active_month只能有 12 个可能的值。在这两种情况下，拥有外键是完全荒谬的。只有一定数量的值，并且根据您所包含的数据的定义，这些值无法更改。就您而言，虽然您可以选择检查约束，但除非您绝对确定
操作的数量永远不会改变< em>外键是正确的方法。在一个稍微独立的问题上，正如 @pst 提到的，我看到你已经被代理键怪物吃掉了。虽然这可以提高性能，但在您设想的大小的表（3个值，
插入/更新/删除）甚至更大的表中，它可以提供所有服务to do 模糊了你想要实现的目标。查看
并了解正在发生的事情并不容易，但是：
更容易阅读；您可能还想考虑对
system列执行相同的操作 - 我可能会这样做，尽管可能值的数量在此略有跳跃。只是我个人对这件事的想法...I think you're confusing the difference between a foreign key constraint and a check constraint.
A foreign key constraint is there to enforce referential integrity and a check constraint constrains a column to containing only valid data. In your case this may seem like a minor difference but if we abstract it slightly I hope to make it clearer.
If we consider a table
userswith the columnsuser_id, user_name, address_id, join_date, active, last_active_month; I recognise that this is not necessarily the best way of doing things but it'll serve for the point I'm trying to make.In this case it's patently ridiculous to have
address_idas a constraint. This column could have any number of values. However,active, assuming we want a booleany/ncan only have two possible values andlast_active_monthcan only have 12 possible values. In both these cases it's completely ridiculous to have a foreign key. There are only a certain number of values and by the definition of the data you are including these values cannot change.In your case, while you could go for a check constraint, unless you can be absolutely certain that the number of
actionswill never change a foreign key is the correct way to go.On a slightly separate matter, and as @pst mentioned, I see you've been eaten by the surrogate key monster. While this can result in performance improvements, in a table of the size you're envisaging ( 3 values,
insert / update / delete) or even a larger one all it serves to do is obscure what you're trying to achieve.It's not easy to look at
and see what's going on, but:
is far easier to read; you may also want to consider doing the same for the
systemcolumn - I probably would, though the number of possible values jumps slightly in this. Just my personal thoughts on the matter...