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.
