.net 如何创建一对一关系的SQL服务器图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4021241/
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
How to create one to one relationship SQL server diagram
提问by user384080
Does anyone know how to create a one-to-one relationship from a SQL Server database diagram?
有谁知道如何从 SQL Server 数据库图表创建一对一关系？
回答by Azhar
You need to put a unique key constraint on top of the foreign key, so its restricted to one-one relationship.
你需要放一个unique key constraint on top of the foreign key，所以它仅限于一对一的关系。
回答by IbrarMumtaz
Assuming you are using Sql Server 2008 onwards and SSMS.
假设您使用的是 Sql Server 2008 和 SSMS。
- The easiest way to do this is simply grab the key from the primary table in the designer surface and drop the key over the secondary table, directly over the primary key.
- A second later, two dialog boxes will pop up, in the active dialog confirm that the two keys involved in the drag and drop process, are actually P.Ks in their respective tables and press O.K.
- Finally, confirm in the second tables any constraints and any other settings that you would want applied like cascading features and etc.
- 最简单的方法是简单地从设计器表面的主表中获取键，并将键放在次表上，直接放在主键上。
- 一秒后，会弹出两个对话框，在活动对话框中确认拖拽过程中涉及的两个键，实际上是各自表中的PK，然后按OK
- 最后，在第二个表中确认您希望应用的任何约束和任何其他设置，如级联功能等。
Once you click O.K again for the second time, SSMS Designer will show a key to key relationship between the tables involved. This is a one-one relationship. Read as, one record in one table directly relates to another record in another table.
第二次再次单击“确定”后，SSMS 设计器将显示所涉及表之间的键对键关系。这是一对一的关系。读作，一个表中的一条记录与另一表中的另一条记录直接相关。
i.e A ResolutionsTable - 1 : 1 - Resolution Types Read as one resolution has one resolution type applied to it, i.e, case closed or case ongoing, case pending law change, case denied? Of course people have different DB design skills so what works for one may not work for another db developer. Never the less the example is clear enough to understand.
ie A ResolutionsTable - 1 : 1 - 决议类型 读作一个决议有一种决议类型适用于它，即案件已结案或案件正在进行、案件待决法律变更、案件被拒绝？当然，人们有不同的 DB 设计技能，所以对一个人有用的可能不适用于另一个数据库开发人员。无论如何，这个例子都足够清晰易懂。
Hopefully this will help any non sql syntax savvy noobies out there like me who prefer to do build an entire database via the SQL Database Diagram feature.
希望这能帮助像我这样喜欢通过 SQL 数据库图表功能构建整个数据库的任何非 sql 语法的菜鸟。
回答by Andamon A. Abilar
if your table is created like this,
如果你的表是这样创建的，
CREATE TABLE tableName (
id INT NOT NULL IDENTITY(1,1) CONSTRAINT[PK:tableName] PRIMARY KEY(id)
, fkId INT NOT NULL
CONSTRAINT[FK:tableName:tableFk]
FOREIGN KEY(fkId)
REFERENCES tableFk(id)
)
CREATE TABLE tableFk (
id INT NOT NULL IDENTITY(1,1) CONSTRAINT[PK:tableFk] PRIMARY KEY(id)
)
you can alter the tableName with this code to set tableName.fkId as unique
您可以使用此代码更改 tableName 以将 tableName.fkId 设置为唯一
ALTER TABLE tableName
ADD UNIQUE (fkId)
回答by Umanda
You can easily use MSSQL Management Studio.
您可以轻松使用 MSSQL Management Studio。
ie
IE
table_user
表用户
uid(pk) username email
uid(pk) 用户名邮箱
table_profile
table_profile
pid(pk) f_name l_name user_id (fk)
pid(pk) f_name l_name user_id (fk)
- Right click on user_id (fk).
- Indexes / Keys...
- you can get Indexes / Keys window.
- 右键单击 user_id (fk)。
- 索引/键...
- 你可以得到索引/键窗口。
then follow this,
然后按照这个，
4 Choose "Add" (From Indexes / Keys window, it will add a new name )
4 选择“添加”（从索引/键窗口，它将添加一个新名称）
5 In "General section" select "Columns" and then select "user_id"
5 在“常规部分”中选择“列”，然后选择“user_id”
6 In "General section" set "Is Unique" as a true
6 在“常规部分”中将“是唯一的”设置为真
7 In "Identity section" give a name for (Name) section. in this case I ll give UK_user_id_profile
7 在“身份部分”中，为（名称）部分命名。在这种情况下，我会给 UK_user_id_profile
8 after all those above steps, close Indexes / Keys window
8 完成上述所有步骤后，关闭索引/键窗口
9 Drag and Drop "uid" (from user table) into "user_id" (from profile table)
9 将“uid”（从用户表）拖放到“user_id”（从配置文件表）
That is it.
这就对了。
The theory behind that, foreign key should be a unique .
背后的理论，外键应该是唯一的。
You need to put a unique key constraint on top of the foreign key, so its restricted to one-one relationship.
您需要在外键之上放置一个唯一键约束，因此它仅限于一对一关系。
That is what Azam said in his post.
这是阿扎姆在他的帖子中所说的。
[In-case, I am using MSSQL 2012]
[以防万一，我使用的是 MSSQL 2012]
Cheers.
干杯。
回答by Nugs
I believe the question was specifically around how to create a one-to-one relation in SQL Server Management Studios Diagram window. I have a procedure where you can create a one-to-one relation in SQL Server Management Studio database diagram.
我相信这个问题特别是围绕如何在 SQL Server Management Studios Diagram 窗口中创建一对一关系。我有一个过程，您可以在 SQL Server Management Studio 数据库图中创建一对一关系。
- Create two tables (A and B), leave table B without a primary key.
- Drag a relation from table A primary key to table B's referenced column (Any matching type column, any name) (This will initially create a one-to-many relation
- Save the diagram (If you do not save here you will likely get a conflict in the change script)
- Go to table B and right click the column and set it as a primary key (This will change the relation to a one-to-one)
- Save the diagram
- 创建两个表（A 和 B），让表 B 没有主键。
- 将关系从表 A 主键拖到表 B 的引用列（任何匹配类型的列，任何名称）（这将最初创建一对多关系
- 保存图表（如果您不在这里保存，您可能会在更改脚本中遇到冲突）
- 转到表 B 并右键单击该列并将其设置为主键（这会将关系更改为一对一）
- 保存图表
You're done!
你完成了！
回答by PatsonLeaner
It's very pretty straight forward when you use the following:
当您使用以下内容时，这是非常直接的：
ALTER TABLE [Salary]
ADD CONSTRAINT FK_Salary_Employee FOREIGN KEY([EmployeeID])
REFERENCES [Employee]([ID]);
That should do the trick!!!
这应该够了吧！！！
