Using the ignore_dup_row and allow_dup_row options

ignore_dup_row and allow_dup_row are options for creating a nonunique, clustered index. These options are not relevant when creating a nonunique, nonclustered index. Since an Adaptive Server nonclustered index attaches a unique row identification number internally, duplicate rows are never an issue—even for identical data values.

ignore_dup_row and allow_dup_row are mutually exclusive.

A nonunique clustered index allows duplicate keys, but does not allow duplicate rows unless you specify allow_dup_row.

If allow_dup_row is set, you can create a new nonunique, clustered index on a table that includes duplicate rows, and you can subsequently insert or update duplicate rows.

If any index in the table is unique, the requirement for uniqueness—the most stringent requirement—takes precedence over the allow_dup_row option. Thus, allow_dup_row applies only to tables with nonunique indexes. You cannot use this option if a unique clustered index exists on any column in the table.

The ignore_dup_row option eliminates duplicates from a batch of data. When you enter a duplicate row, Adaptive Server ignores that row and cancels that particular insert or update with an informational error message. After the cancellation, any transaction that may have been active at the time continues as though the insert or update had never taken place. Nonduplicate rows are inserted normally.

The ignore_dup_row applies only to tables with nonunique indexes: you cannot use this keyword if a unique index exists on any column in the table.