SQL SQL两个表并创建一个链接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14978244/
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 two tables and creating a link table
提问by Sam
I have two tables: Employee (ID, Name, Address) and Store(ID,Address) and I would like to record information about people who work in each store.
我有两个表：Employee (ID, Name, Address) 和 Store(ID,Address)，我想记录每个商店工作人员的信息。
I thought of making a new table called Employee_List table. My questions:
我想制作一个名为 Employee_List 表的新表。我的问题：
1- Employee_List and Employee has one-to-many relation, right?
1- Employee_List 和 Employee 是一对多的关系，对吧？
2- Employee_list to store has one-to-one relation, right?
2- 要存储的 Employee_list 是一对一的关系，对吗？
3- How to define foreign and primary keys for Employee_List table?
3- 如何为 Employee_List 表定义外键和主键？
回答by Darren
Employee_list should have:
Employee_list 应该有：
- employee_listid (INT PK)
- employee_id (INT FK)
- store_id (INT FK)
- 员工列表ID（INT PK）
- 员工 ID (INT FK)
- store_id (INT FK)
I would recommend changing the table name to represent the composite table, i.e.
EmployeeStores. This would allow your schema to be scalable, employees can work in multiple stores.
我建议更改表名以表示复合表，即
EmployeeStores. 这将使您的架构具有可扩展性，员工可以在多个商店工作。
In SQL Server:
在SQL Server：
CREATE TABLE EmployeeStores
(
EMPLOYEEStoreID INT IDENTITY,
EMPLOYEEID INT FOREIGN KEY REFERENCES Employee(employee_id),
STOREID INT FOREIGN KEY REFERENCES Store(store_id)
)
