Replicate tables without primary keys
Get steps to convert your table without a primary key in this tip, one of 15 replication tips in 15 minutes.
The following is one of 15 replication tips in 15 minutes. Click here to return to the full list of tips.
You aren't necessarily forced to use snapshot or merge replication if your tables don't have primary keys. I never have seen an instance where it was impossible to add a primary key or find candidate or surrogate primary keys on the tables that you could convert to a primary key. This is not to say that such a scenario does not exist, but if your database is designed according to relational principles, it is unlikely.
This script illustrates how to do this:
xp_cmdshell 'del C:"Program FilesMicrosoft SQL Server"MSSQLdatascript1_log.ldf' create database script1 go use script1 go create table table1 (charcol1 char(20), charcol2 char(20), charcol3 char(20)) go create table table2 (charcol1 char(20), charcol2 char(20), charcol3 char(20)) go create table table3 (charcol1 char(20), charcol2 char(20), charcol3 char(20)) go sp_dboption 'script1','published','true' go sp_addpublication 'script1',@status='active' go sp_addpublication_snapshot 'script1' go sp_addarticle 'script1','table1','table1' go --this will generate the error message --Server: Msg 14088, Level 16, State 1, Procedure sp_addarticle, Line 699 --The table '[dbo].[table1]' must have a primary key to be published using the transaction-based method. sp_addlogin 'test','password','script1' go sp_adduser 'test','test','db_owner' go sp_changeobjectowner 'table1','test' go sp_changeobjectowner 'table2','test' go sp_changeobjectowner 'table3','test' go alter table test.table1 add pk int not null identity constraint pk1 primary key go alter table test.table2 add pk int not null identity constraint pk2 primary key go alter table test.table3 add pk int not null identity constraint pk3 primary key go sp_addarticle 'script1','table1','test.table1' go --this time the add article works! --creating a view for the application to talk to create view dbo.table1 as select charcol1, charcol2, charcol3 from test.table1 go
Click here to return to 15 replication tips in 15 minutes.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.