1 follower Follow

No keys on primary with duplicate rows


A third party application has tables with no primary or unique keys and there are duplicate rows of data in the table. How can Replicate handle this if we want all rows updated on the target when we update all rows on source?


There is a business decision that needs to be made. If an update is done on a source table (no unique/no primary key) that updates 3 rows, is it desired to have the same change made on the target?

  • If so, then you would need to set up a conflict hander for these tables with no primary keys or unique keys.
  • If not, then these would need to be manually resolved because Replicate will see this as a conflict and pause the APPLY process until action can be taken manually.

To propagate this multiple row update for a table, the conflict handler that would be needed is below. If you have a set you can put them in a file and call that file. For example: dbvrep>@conflict_handlers

Handlers are:

dbvrep> SET_CONFLICT_HANDLERS FOR TABLE <owner.tablename> FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE <owner.tablename> FOR UPDATE ON TOO_MANY TO SQL s/$/ and rownum = 1/
dbvrep> shutdown apply

Restart APPLY from command line on the target host.

Please also refer to the User Guide section below for more detail:

General information on Conflict Resolution can also be found in the User Guide:

Donna Zehl Answered

Please sign in to leave a comment.