SQL Server Management Studio - 添加外键令人困惑？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3896625/
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
SQL Server Management Studio - adding foreign key confusing?
提问by tvr
I always find it confusing to add foreign keys to primary table in Management Studio.
我总是觉得在 Management Studio 中向主表添加外键很混乱。
Lets say I have a
假设我有一个
Table1
{
ID int, -- Primary Key
Table2ID int, -- Refers to Table2's ID
}
Table2
{
ID int, -- Primary Key
SomeData nvarchar(50)
}
I am adding a foreign key to Table1 by Right Click -> Relationships -> Table and column specification. I am setting "Primary" popups to Table2, ID and "Foreign Key Table" to Table1, Table2ID.
我正在向 Table1 添加外键Right Click -> Relationships -> Table and column specification。我将“主要”弹出窗口设置为 Table2、ID，将“外键表”设置为 Table1、Table2ID。
My questions:
我的问题：
Shouldn't Table2 be listed for "Foreign Key Table" and Table1 for Primary Key? Is my understanding wrong?
When I save I get an alert "The following tables will be saved to your database." and it shows both tables. I really don't get this. I only changed Table1. Why is the second table shown?
不应该为“外键表”列出 Table2，为主键列出 Table1？我的理解有误吗？
当我保存时，我收到一条警告“以下表格将保存到您的数据库中。” 它显示了两个表。我真的不明白这个。我只改变了表1。为什么显示第二个表？
回答by Zach
- Click the expand symbol next to the table.
- Right click on the "Keys" folder and select "New Foreign Key."
- (Alternatively, you can click the Relationships button on the toolstrip when you have the table open)
- Click the "..." button on the "Tables and Columns Specifications" row to open the editor.
- The drop down on the left will be the table you're adding from, and the static text field will list the table you're adding to.
- Use the dropdowns to specify your constraints, and be sure both sides have the same number of columns.
- 单击表格旁边的展开符号。
- 右键单击“Keys”文件夹并选择“New Foreign Key”。
- （或者，您可以在打开表格时单击工具条上的“关系”按钮）
- 单击“表和列规范”行上的“...”按钮以打开编辑器。
- 左侧的下拉列表将是您添加的表格，静态文本字段将列出您要添加到的表格。
- 使用下拉菜单指定您的约束，并确保两侧的列数相同。
回答by marc_s
Why don't you just use the equivalent T-SQL statements?? Seems much easier and less confusing to me:
为什么不直接使用等效的 T-SQL 语句？？对我来说似乎更容易，更容易混淆：
ALTER TABLE dbo.Table1
ADD CONSTRAINT FK_Table1_Table2
FOREIGN KEY(Table2ID) REFERENCES dbo.Table2(ID)
When I read this, I believe this is immediately clear what two tables are involved, and how they are connected (Table1.Table2ID--(references)--> Table2.ID)
当我阅读本文时，我相信这会立即清楚所涉及的两个表以及它们是如何连接的（Table1.Table2ID--(references)--> Table2.ID）
If you want to stay in the SSMS designer world: you could also create a database diagram of your tables involved, and then just drag&drop your Table2IDcolumn from Table1over to Table2and drop it onto the IDcolumn there - this would graphically tell SSMS what you want to do, and you just need to review your choices and click OK on the dialog that pops up.
如果您想留在 SSMS 设计器的世界中：您还可以创建所涉及表的数据库图表，然后只需将您的Table2ID列从Table1上方Table2拖放到ID那里，然后将其拖放到那里的列上 - 这会以图形方式告诉 SSMS 您想要做什么，您只需要查看您的选择，然后在弹出的对话框中单击“确定”。
回答by Ann L.
1.Shouldn't Table2 be listed for "Foreign Key Table" and Table1 for Primary Key? Is my understanding wrong?
1.“外键表”不应该列表2，主键不应该列表1吗？我的理解有误吗？
I believe your understanding is wrong. Table2 is the table whose primary key you are referencing. Therefore it's listed under Primary Key. Table1 is the table that will have the foreign key (the reference to the primary key of another table); therefore it's listed under "Foreign Key Table".
我相信你的理解是错误的。Table2 是您要引用其主键的表。因此它列在主键下。Table1 是将具有外键的表（对另一个表的主键的引用）；因此它列在“外键表”下。
As far as why both tables are saved, even though the foreign key is listed afterward as belonging to Table1: I believe it's because the foreign key constrains both tables. They both have to "know" about the constraint, so they both need to be saved.
至于为什么保存两个表，即使外键随后被列为属于 Table1：我相信这是因为外键约束了两个表。他们都必须“知道”约束，所以他们都需要被保存。
回答by Deepesh
ALTER TABLE dbo.Table1
ADD CONSTRAINT FK_Table1_Table2
FOREIGN KEY(Table2ID) REFERENCES dbo.Table2(ID)
