Primary key in transactional replication

Question

Primary key in transactional replication

sree-226516

SSCrazy

Points: 2590
More actions
May 10, 2006 at 9:04 am

#168283

Hi,
I have a question in Transactional replication.What happends to the primary key when Table with primary key gets replicated to subscriber.Is primary key still exsist in subscrbed table?Any help is appreciated.
Thanks,
SR

Thanks,
SR

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Yes, the primary key exists in the Subscriber table. Replication needs the PK in order to retrieve the subscription records to apply the replicated transactions. You do have other options when creating the intial snapshot, such as bringing over referential integrity, clustered indexes, triggers, extended properties.

sree-226516 SSCrazy Points: 2590 More actions · Answer 2

Thanks a lot for your reply.I will really appreciate if you can explain me in little detail or guide me to the proper article about how primary keys and identity columns works in transactional replicataion.

Thanks in advance.

SR

Thanks,
SR

Mark Yelton SSCommitted Points: 1570 More actions · Answer 3

When you create the table on the Subscriber, use of this clause for the Identity column is required: NOT FOR REPLICATION

Indicates that the IDENTITY property should not be enforced when a replication login such as sqlrepl inserts data into the table. Replicated rows must retain the key values assigned in the publishing database; the NOT FOR REPLICATION clause ensures that rows inserted by a replication process are not assigned new identity values. Rows inserted by other logins continue to have new identity values created in the usual way. It is recommended that a CHECK constraint with NOT FOR REPLICATION also be defined to ensure that the identity values assigned are within the range wanted for the current database.

I've never used the CHECK constraint part of this excerpt from BOL. I didn't need to because I used the proper values when creating the tables on the Publisher and Subscriber. If you are using Bi-directional Replication, you'll need to do it on both sides.

HTH,

Mark

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 4

A Guide to SQL Server 2000 Transactional & Snapshot Replication by Hillary Cotter is excellent. We have multiple replication streams running with a Central Distributor. Found this book excellent when setting the whole lot up. Regards Derek.