SQL Server 事务复制和不同的主键
使用 SQL Server 2005 和事务复制，我可以删除订阅者的主键约束，同时保留发布者的主键约束吗？
主要我想这样做是因为我想在与现有集群约束不同的列上集群。 我认为在不先删除约束的情况下，我无法将约束从集群转换为非集群，并且复制已经发生。
With SQL Server 2005 and transactional replication, can I remove the primary key constraints on the subscriber, while leaving the primary key constraints on the publisher?
Primary I want to do this because I want to cluster on different columns than the existing clustered constraints. I don't think I can convert a constraint from clustered to nonclustered without dropping it first, and replication is already occurring.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
为什么不保留主键并在订阅者上创建额外的非聚集索引，否则这不会解决您的问题？ 如果在订阅者上对其他列建立索引的原因是性能，那么这应该是一个解决方案。
Why don't you leave the primary key and create additional non clustered indexes on the subscriber or will this not resolve your problem? If the reason to have other columns indexed on the subscriber is performance then this should be a solution.
这可能适用于快照复制，但我不确定它是否适用于事务复制。 允许复制表的唯一要求是存在主键，以允许唯一标识每一行。
您可以尝试暂停复制，然后尝试删除主键约束，将其重新创建为非集群 PK，然后取消暂停复制。 如果 SQL Server 不允许您删除 PK，您会在造成任何损害之前发现这一点。
另一种方法是中断复制并重新初始化它。
无论哪种方式，您都需要在维护时段内进行更改。
This might work for snapshot replication, but I'm not sure it would work with transactional replication. The sole requirement on a table to allow it to be replicated is the existence of a primary key to allow each row to be uniquely identified.
You could try pausing replication and then try dropping the primary key constraint, recreating it as a non-clustered PK and then un-pausing replication. If SQL Server will not allow you to drop the PK, you'll find out before you do any damage.
The alternative is to break replication and reinitialise it.
Either way, you'll want to do the change during a maintenance window.
直接，对于主键来说这是不可能的。 反之亦然：当您设置事务复制时，当您选择要复制的文章时，您可以选择不同的属性，例如“检查外键约束”。 将该属性设置为 false。 在 db1 中，将主键转换为外键，并使用一些包含主键的新表 tb1 。 因此，最终，在您的复制数据库 db2 上，外键约束将不会被复制，从而允许您执行您想要的操作。
Directly, for primary key its not possible. Other way round: When you set the transactional replication, when you select the articles for replication, you can select different properties like "check foreign key contstraint". Set that property to false. In db1 convert your primary key to a foreign key with some new table tb1 containing that as primary key. So, ultimately , on your replication db db2, the foreign key constraint wont be replicated and thus allowing you to do what you want.
复制过程的基础是在不同服务器之间维护相同的数据库组织。
你在这里的问题可以被认为是询问是否可以使用复制过程来打破这个基本的复制原则。
所以答案是否定的，但我仍然对让你提出这个问题的原因感兴趣。 我应该说这个“双主键”选项被视为解决另一个问题的一种方法吗？ 我认为你应该回到这个最初的问题并尝试找到另一种方法来解决它。
The basic of replication process is to maintain identical database organisation between different servers.
Your question here can be considered as asking if a replication process can be used to break this basic replication principle.
So the answer is no, but I am still interested in the reasons that let you formulate this question. Shall I say that this "dual primary key" option was seen as a way to solve another problem? I think you should go back to this initial issue and try to find another way to solve it.
我做了自己的功课，并得出结论：您可以放弃对订户的限制。
我设置了一个简单的事务复制场景，将主键删除到订阅者上，然后执行一些插入、删除和更新，并验证更改是否已复制到订阅者。
我想我一开始就应该这样做。 我不知道这会这么容易:)。
I did my own homework, and came to the conclusion that you can drop the constraints on the subscriber.
I set up a simple transactional replication scenario, dropped the primary key on the subscriber, and then did some inserts, deletes and updates, and verified that the changes were replicated to the subscriber.
I guess I should have done that to begin with. I didn't know it was going to be that easy :).