Replication of Deferred Updates on Primary Keys

Updates on tables that have a unique column index are not supported by traditional replication, and the Replication Server reports errors.

For example, table t has a unique index on column c, with values: 1, 2, 3, 4 and 5. A single update statement is applied to the table:

update t set c = c+1

Using traditional replication, this statement results in:

update t set c = 2 where c = 1
update t set c = 3 where c = 2
update t set c = 4 where c = 3
update t set c = 5 where c = 4
update t set c = 6 where c = 5

The first update attempts to insert a value of c=2 into the table; however, this value already exists in the table. Replication Server displays error 2601—an attempt to insert a duplicate key.

You can use SQL statement replication to address this issue. If the table has a unique index, and SQL statement replication is configured for update statements, the Adaptive Server replicates the update using SQL statement replication.