使用 SQL Server 创建一对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1308339/
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
Create a one to many relationship using SQL Server
提问by Tim
How do you create a one to many relationship using SQL Server?
如何使用 SQL Server 创建一对多关系？
回答by OMG Ponies
- Define two tables (example A and B), with their own primary key
- Define a column in Table A as having a Foreign key relationship based on the primary key of Table B
- 定义两个表（例如 A 和 B），具有自己的主键
- 将表 A 中的列定义为具有基于表 B 主键的外键关系
This means that Table A can have one or more records relating to a single record in Table B.
这意味着表 A 可以有一个或多个与表 B 中的单个记录相关的记录。
If you already have the tables in place, use the ALTER TABLE statement to create the foreign key constraint:
如果您已经有了这些表，请使用 ALTER TABLE 语句创建外键约束：
ALTER TABLE A ADD CONSTRAINT fk_b FOREIGN KEY (b_id) references b(id)
fk_b: Name of the foreign key constraint, must be unique to the databaseb_id: Name of column in Table A you are creating the foreign key relationship onb: Name of table, in this case bid: Name of column in Table B
fk_b: 外键约束的名称，必须是数据库唯一的b_id：表 A 中的列的名称，您要在其上创建外键关系b: 表名，在本例中为 bid: 表 B 中的列名称
回答by Thorarin
This is a simple example of a classic Order example. Each Customercan have multiple Orders, and each Ordercan consist of multiple OrderLines.
这是一个经典订单示例的简单示例。每个Customer可以有多个Order，每个Order可以由多个OrderLine组成。
You create a relation by adding a foreign keycolumn. Each Order record has a CustomerID in it, that points to the ID of the Customer. Similarly, each OrderLine has an OrderID value. This is how the database diagram looks:
您可以通过添加外键列来创建关系。每个订单记录中都有一个 CustomerID，指向客户的 ID。同样，每个 OrderLine 都有一个 OrderID 值。这是数据库图的外观：
In this diagram, there are actual foreign key constraints. They are optional, but they ensure integrity of your data. Also, they make the structure of your database clearer to anyone using it.
在这个图中，有实际的外键约束。它们是可选的，但它们可确保数据的完整性。此外，它们使使用它的任何人都可以更清楚地了解数据库的结构。
I assume you know how to create the tables themselves. Then you just need to define the relationships between them. You can of course define constraints in T-SQL (as posted by several people), but they're also easily added using the designer. Using SQL Management Studio, you can right-click the Ordertable, click Design(I think it may be called Edit under 2005). Then anywhere in the window that opens right-click and select Relationships.
我假设您知道如何自己创建表。然后你只需要定义它们之间的关系。您当然可以在 T-SQL 中定义约束（由几个人发布），但它们也可以使用设计器轻松添加。使用SQL Management Studio，可以右击Order表，点击Design（我觉得2005下可能叫Edit）。然后在打开的窗口中的任何地方右键单击并选择Relationships。
You will get another dialog, on the right there should be a grid view. One of the first lines reads "Tables and Columns Specification". Click that line, then click again on the little [...] button that appears on the right. You will get this dialog:
您将获得另一个对话框，右侧应该有一个网格视图。第一行之一是“表格和列规范”。单击该行，然后再次单击右侧出现的小 [...] 按钮。你会得到这个对话框：
The Ordertable should already be selected on the right. Select the Customer table on the left dropdown. Then in the left grid, select the IDcolumn. In the right grid, select the CustomerIDcolumn. Close the dialog, and the next. Press Ctrl+Sto save.
该订单表应该已经在正确的选择。在左侧下拉列表中选择 Customer 表。然后在左侧网格中，选择该ID列。在右侧网格中，选择CustomerID列。关闭对话框，然后下一步。按Ctrl+S保存。
Having this constraint will ensure that no Order records can exist without an accompanying Customer record.
拥有此约束将确保没有随附的客户记录就不会存在任何订单记录。
To effectively query a database like this, you might want to read up on JOINs.
要有效地查询这样的数据库，您可能需要阅读 JOINs。
回答by KSimons
This is how I usually do it (sql server).
这就是我通常这样做的方式（sql server）。
Create Table Master (
MasterID int identity(1,1) primary key,
Stuff varchar(10)
)
GO
Create Table Detail (
DetailID int identity(1,1) primary key,
MasterID int references Master, --use 'references'
Stuff varchar(10))
GO
Insert into Master values('value')
--(1 row(s) affected)
GO
Insert into Detail values (1, 'Value1') -- Works
--(1 row(s) affected)
insert into Detail values (2, 'Value2') -- Fails
--Msg 547, Level 16, State 0, Line 2
--The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Detail__MasterID__0C70CFB4".
--The conflict occurred in database "Play", table "dbo.Master", column 'MasterID'.
--The statement has been terminated.
As you can see the second insert into the detail fails because of the foreign key. Here's a good weblink that shows various syntax for defining FK during table creation or after.
正如您所看到的，由于外键，第二次插入细节失败。这是一个很好的网络链接，它显示了在表创建期间或之后定义 FK 的各种语法。
回答by andrewWinn
If you are not using SSMS then here is the syntax:
如果您没有使用 SSMS，那么这里是语法：
ALTER TABLE <table_name>
ADD <constraint_name> FOREIGN KEY
(<column_name1> ,
<column_name2> )
REFERENCES <table_name>
(<column_name1> ,
<column_name2>)
回答by DevByDefault
If you are talking about two kinds of enitities, say teachers and students, you would create two tables for each and a third one to store the relationship. This third table can have two columns, say teacherID and StudentId. If this is not what you are looking for, please elaborate your question.
如果您谈论两种实体，例如教师和学生，您将为每个实体创建两个表，并创建第三个表来存储关系。第三个表可以有两列，比如teacherID 和StudentId。如果这不是您要查找的内容，请详细说明您的问题。
