SQL 对另一个模式中表的外键引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2095268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign key reference to table in another schema
提问by Peter Lang
I tried to create a foreign key on one of my tables, referencing a column of a table in a different schema.
我试图在我的一个表上创建一个外键，引用不同模式中的表的列。
Something like that:
类似的东西：
ALTER TABLE my_schema.my_table ADD (
CONSTRAINT my_fk
FOREIGN KEY (my_id)
REFERENCES other_schema.other_table(other_id)
)
Since I had the necessary grants, this worked fine.
由于我获得了必要的资助，所以效果很好。
Now I wonder if there are reasons for not referencing tables in a different schema, or anything to be careful about?
现在我想知道是否有理由不引用不同模式中的表，或者有什么需要注意的地方？
采纳答案by Randy Minder
No problem doing this. Schemas really have no impact when establishing foreign key relationships between tables. Just make sure the appropriate people have the permissions necessary for the schemas you intend to use.
这样做没有问题。在表之间建立外键关系时，模式确实没有影响。只需确保合适的人员拥有您打算使用的架构所需的权限。
回答by Jim Hudson
If you're in an organization where different people have authority over different schemas, I think it's good practice to give the other schema the ability to disable, or even drop and recreate, your constraint.
如果您所在的组织中不同的人对不同的模式拥有权限，我认为让其他模式能够禁用甚至删除和重新创建您的约束是一种很好的做法。
For example, they could need to drop or truncate their table and then reload it to handle some (very weird) support issue. Unless you want to get called in the middle of the night, I recommend giving them the ability to temporarily remove your constraint. (I also recommend setting your own alerts so that you'll know if any of your external constraints get disabled or dropped). When you're crossing organizational/schema lines, you want to play well with others. The index that Vincent mentioned is another part of that.
例如，他们可能需要删除或截断他们的表，然后重新加载它以处理一些（非常奇怪的）支持问题。除非您想在半夜接到电话，否则我建议让他们能够暂时解除您的限制。（我还建议您设置自己的警报，以便您知道是否有任何外部约束被禁用或删除）。当您跨越组织/模式界限时，您希望与他人相处融洽。Vincent 提到的索引是其中的另一部分。
回答by Vincent Malgrat
This will work exactly as a foreign key that references a table in its own schema.
这将完全作为引用其自己模式中的表的外键。
As with regular foreign keys, don't forget to index my_idif the parent key is ever updated or if you delete entries from the parent table (unindexed foreign keys can be a source of massive contention and the index is usually useful anyway).
与常规外键一样，my_id如果父键被更新或从父表中删除条目，请不要忘记索引（未索引的外键可能是大量争用的来源，无论如何索引通常很有用）。
回答by OMG Ponies
The only thing I ran into was making sure the permission existed on the other schema. The usual stuff - if those permission(s) disappear for whatever reason, you'll hear about it.
我遇到的唯一问题是确保权限存在于另一个架构上。通常的事情 - 如果这些权限因任何原因消失，你会听到它。
回答by David Oneill
One reason this can cause problems is you need to be careful to delete things in the right order. This can be good or bad depending on how important it is to never have orphans in your tables.
这可能导致问题的原因之一是您需要小心地以正确的顺序删除内容。这可能是好是坏，这取决于在您的表中永远不要有孤儿的重要性。
