Duplicate Rows in Updated Tables
If a table allows duplicates, rows can always be updated to duplicate other rows.
If a table does not allow duplicates:
• Rows can be updated to duplicate other rows if the table uses a heap storage structure.
• Rows cannot be updated to duplicate other rows if the table is a keyed structure. The update is rejected and an error is generated.
If you use the following “bulk increment” update in which the info column has values 1, 2, 3, and so on, every row in the table is updated:
update data set info = info+1;
If duplicates are not allowed, this update fails due to duplicate rows being created.
The new values for the first row are prepared, changing the info column value from 1 to 2. Before inserting the new values, a check is made to see if they violate any constraints. Because the new value, 2, duplicates the value in an existing row, thus violating the criterion stating that duplicates are not allowed, an error is generated and the update is rolled back.
To solve this problem, use either of the following techniques:
• Allow duplicates when creating the table
• Modify the table to use a heap storage structure before performing the update