将外键添加到现有数据库
我正在尝试将数据从 Excel 电子表格导出到相当复杂的关系数据库中。电子表格通过说明其他对象的名称来指示“外键”。 （幸运的是，我对电子表格有一定的控制权，因此我可以保证这些名称是唯一的，并且它们引用的对象确实存在）。
我有一个程序可以在 MSSql 数据库中重新创建这些表，但它不能自动将它们相互链接。此外，我不想使用对象的实际名称作为主键，因为最终数据库会很大。
因此，如果我有许多现有但未连接的表，它们通过“名称”字段相互引用，那么如何添加通过 ID 链接它们的外键？
我所拥有的简化版本：
Parent
ID: 1 (PK)
Name: Mary
Child
ID: 2 (PK)
Name: Jane
ParentName: Mary
以及我想要实现的目标：
Child
ID: 2 (PK)
Name: Jane
ParentID: 1 (FK)
感谢您的帮助！我无法找到如何在事后或在不同字段上添加外键映射的示例。
I'm trying to export data from an Excel spreadsheet into a fairly complex relational database. The spreadsheet indicates "foreign keys" by stating the names of other objects. (Luckily, I have some control over the spreadsheet, so I can guarantee these names are unique AND that the objects they reference actually exist).
I have a program that can recreate these tables in a MSSql database, but it can't automatically link them to each other. Besides, I don't want to use the actual names of the objects as the primary key since eventually the database will be large.
So, if I have many existing but unconnected tables which refer to each other by their "name" fields, how can I add a foreign key that links them by their IDs?
A simplified version of what I have:
Parent
ID: 1 (PK)
Name: Mary
Child
ID: 2 (PK)
Name: Jane
ParentName: Mary
And what I want to achieve:
Child
ID: 2 (PK)
Name: Jane
ParentID: 1 (FK)
Thanks for any help! I wasn't able to find an example of how to add a foreign key mapping after the fact, or on a different field.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
请参阅 MSSQL 的
ALTER TABLE语法。您可以想出类似的方法来将约束添加到表中：然后，一旦约束存在，请尝试以下操作：
如果您可以保证父级的
Name是唯一的，那么这应该可行。否则，您可以将LIMIT 1添加到查询末尾。但是，如果有多个父级具有相同的Name，您将需要添加额外的逻辑（原始帖子中未指定）。See the
ALTER TABLEsyntax for MSSQL. You can come up with something like this to add the constraint to the table:Then once the constraint is in, try something like:
That should work if you can guarantee the
Nameof the Parent is unique. Otherwise you can addLIMIT 1to the end of the query. But if there are multiple Parents with the sameName, you're going to need to add extra logic (which isn't specified in your original post).由于您将定期执行此操作，因此我认为您应该导入到临时表中。我喜欢将临时表隔离在它们自己的模式中。
使用临时表检索或生成所需的键，然后根据临时表中的数据插入/更新 OLTP 表。最后，截断暂存表。
Since you're going to be doing this regularly, I think you should import into a staging table. I like to isolate staging tables in their own schema.
Use the staging table to retrieve or generate the keys you need, then insert/update your OLTP tables based on the data in the staging table. Finally, truncate the staging table.