Replicate tables without primary keys

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.

1 of 15 replication tips: Replicate tables without primary keys

Be careful about adding a primary key to existing tables -- you might break the application that uses these tables. Adding a column has the potential to cause unqualified insert selects to fail. Here is an example of an unqualified insert select:

Insert into TableName1
Select * from TableName2

To be able to do an unqualified insert select, TableName1 must have columns of the same or compatible data types in the same order as TableName2. Adding a column to TableName2 will cause this unqualified insert to fail. If you add a primary key, you should use an int data type (small int, bigint, float, real will also work), and assign the identity property to this column. If all the columns in your select statement are named, you will not have a problem with insert selects.

It is also possible to convert your table without a primary key to a view and have a base table beneath the view with a primary key on an additional identity column. Here are the steps to do this:

• Create a new login and database users. We'll call it "test."
• Change the object owner of the tables to "test."
• Create views on the base tables with the original table name and the object owner dbo.
• Add an int column to these tables with a pk constraint on them.

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.