在 SQL 中复制与身份字段的一对一关系
假设您有两个处于一对一关系的表；即子表的主键也是将其链接到父表的外键。还假设父级的主键是标识字段（插入记录时数据库分配的单调递增整数）。
假设您需要将这两个表中的记录复制到第二对相同的表中——父表的主键是一个标识，而将子表链接到父表的外键也是子表的主键。
我应该如何将记录从一组表复制到另一组表？
我目前有三种解决方案，但我想知道是否还有其他更好的解决方案。
选项 1：暂时禁用 目的地的身份属性 父表。从以下位置复制记录 父表，然后子表， 保持相同的值 主键。交叉你的手指 没有冲突（值 源表的主键已经存在 存在于目标表中）。
选项 2：临时添加一列 要保存的目标父表 “旧”（源）主键。复制 来自父表的记录， 允许数据库分配新的 主键但保存旧的 临时列中的主键。 从子表中复制记录， 将源子表连接到 目标父表通过 旧主键，使用连接 将记录插入到 带有新的目标子表 主键。放下临时的 来自目标父级的列 表。
选项 3：按顺序复制 逐条记录，首先来自父级 给父母，然后给孩子，使用 DB提供的“最后的身份” 插入记录”功能以确保 链接已保留。
在这些选项中，我认为选项 2 是我的首选。有人更喜欢其他两种选择之一吗？如果是，为什么？有人有“更好”的不同解决方案吗？
Suppose you have two tables that are in a one-to-one relationship; i.e. the primary key of the child table is also the foreign key that links it to the parent table. Suppose also that the primary key of the parent is an identity field (a monotonically increasing integer that is assigned by the database when the record is inserted).
Suppose that you need to copy records from these two tables into a second pair of identical tables -- the primary key of the parent is an identity, and the foreign key linking the child to the parent is also the child's primary key.
How should I copy records from one set of tables to the other set?
I currently have three solutions, but I'd like to know if there are others that are better.
Option 1: Temporarily disable the
identity property in the destination
parent table. Copy records from the
parent table, then the child table,
keeping the same values for the
primary key. Cross your fingers that
there are no conflicts (value of
primary key of source table already
exists in destination table).Option 2: Temporarily add a column to
the destination parent table to hold
the "old" (source) primary key. Copy
records from the parent table,
allowing the database to assign a new
primary key but saving the old
primary key in the temporary column.
Copy records from the child table,
joining the source child table to the
destination parent table via the the
old primary key, using the join to
insert the record into the
destination child table with the new
primary key. Drop the temporary
column from the destination parent
table.Option 3: Copy sequentially
record-by-record, first from parent
to parent, then child to child, using
DB-provided "identity of last
inserted record" functions to ensure
that the link is maintained.
Of these options, I think option 2 is my preference. Does anyone prefer one of the other two options, and if so, why? Does anyone have a different solution that is "better"?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这就是为什么记住这一点如此重要的原因之一：即使您使用代理键（如身份列），您也始终需要业务密钥。即，表上总是需要一些其他唯一约束。如果您有这种情况，那么另一种选择是将这些值插入到不带标识值的父表的副本中，并使用该唯一键为子行插入正确的父值。
如果您没有该唯一密钥，那么考虑到您的情况，我同意您的最佳解决方案可能是选项#2。
This is one reason why it is so critical to remember that even if you use a surrogate key (like an identity column), you always need a business key. I.e., there always need to be some other unique constraint on the table. If you had that, then another choice would be to insert the values into the copy of the parent table without the identity values and use that unique key to insert the proper parent value for the child rows.
If you do not have that unique key, then given your situation, I agree that your best solution would likely be Option #2.
在决定将数据复制到新的表集的方法之前，您应该调查以下项目：
根据答案，您应该能够选择满足应用程序要求的正确解决方案。
Before you decide on an approach to copy data to new set of tables, you should investigate following items:
Based on the answers, you should be able to pick the right solution that will meet the requirements of the application.
我的钱花在选项 1 上（请参阅
SET IDENTITY INSERT， http://msdn.microsoft.com/en-us/library/ms188059.aspx）。但是：你为什么要复制它们？
ALTER TABLE。但要回答你的问题，请务必使用选项 1。
My money is on Option 1 (see
SET IDENTITY INSERT, http://msdn.microsoft.com/en-us/library/ms188059.aspx).But: Why are you copying them?
ALTER TABLE.But to answer your question, use Option 1, definitely.