locked
Table primary key and Replication RRS feed

  • Question

  • When setting up a database for Replication on SQL 2003 x 64 there was a message stating that certain tables could not be replicated because they do not have a primary key. Upon investigating the table I have discovered that these tables that could not be replicated have a multi field primary key. What is the deal with replication and a table's primary key?
    Wednesday, June 22, 2011 2:05 PM

Answers

  • All published tables in transactional replication must contain a declared primary key. Existing tables can be prepared for publishing by adding a primary key using the Transact-SQL statement..plz check below link

    http://msdn.microsoft.com/en-us/library/ms151254.aspx

     

    • Proposed as answer by Peja Tao Friday, June 24, 2011 8:01 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 8:57 AM
    Thursday, June 23, 2011 2:36 AM
  • For transactional replication, all tables which needs to be published need to have a primary key. The primary key is primarily used to compare the data between publisher and subscriber. For example in the case of a delete statement, only the primary key is passed with the delete command to subscriber.

    Now regarding Multi Column primary key, i dont think it has any such constraints, I was able to create publication with Multi Column Primary Keys.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    • Proposed as answer by Peja Tao Friday, June 24, 2011 8:01 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 8:57 AM
    Thursday, June 23, 2011 3:00 AM

All replies

  • All published tables in transactional replication must contain a declared primary key. Existing tables can be prepared for publishing by adding a primary key using the Transact-SQL statement..plz check below link

    http://msdn.microsoft.com/en-us/library/ms151254.aspx

     

    • Proposed as answer by Peja Tao Friday, June 24, 2011 8:01 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 8:57 AM
    Thursday, June 23, 2011 2:36 AM
  • For transactional replication, all tables which needs to be published need to have a primary key. The primary key is primarily used to compare the data between publisher and subscriber. For example in the case of a delete statement, only the primary key is passed with the delete command to subscriber.

    Now regarding Multi Column primary key, i dont think it has any such constraints, I was able to create publication with Multi Column Primary Keys.

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    • Proposed as answer by Peja Tao Friday, June 24, 2011 8:01 AM
    • Marked as answer by Peja Tao Wednesday, June 29, 2011 8:57 AM
    Thursday, June 23, 2011 3:00 AM