Error 1531

Severity

Message text

The sorted_data option cannot be used for a nonclustered index if the keys are not unique within the table. Create index was aborted because of duplicate keys. Primary key is '%S_KEY'.

Explanation

This error occurs when you try to create a nonunique, nonclustered index with the sorted_data option on a column or columns containing rows with the same index value. If this situation exists, a statement of the following type will fail:

1> create index <index_name>
2> on <table_name>(<col1>, <col2>, <col3>)
3> with_sorted_data
4> go

When error 1531 occurs, no index is created.

Action

To avoid the 1531 error, do one of the following:

Do not use the sorted_data option, or
Delete rows with duplicate index values.

If you decide to delete rows with duplicate index values, use the following procedure.

For a brief time, some rows from the table will exist only in a temporary table, and if a power failure or other interruption occurs, you could lose the temporary table and the rows in it. To protect yourself against data loss, you can make a backup copy of the table before you begin the procedure. Alternatively, you can modify the procedure to use a specific table instead of a temporary table.

Find the rows that caused the 1531 error:

1> select * from <table_name>
2> group by <col1>, <col2>, <col3>
3> having count (*) > 1 
4> go

Put one instance of each distinct row from step 1 into a temporary table:

1> select distinct * into #<temp_table_name> 
2> from <table_name> 
3> group by <col1>, <col2>, <col3> having count(*)> 1 
4> go

Some of the rows may contain identical values in the column or columns to be indexed and different values in other columns. Use the following query to find these rows:
```
1> select * from #<temp_table_name> 
2> group by <col1>, <col2>, <col3> having count(*)> 1 
3> go
```
Examine the individual rows to decide which rows should be deleted. Delete these rows from the temporary table now. The rows you delete in this step will be deleted from the original table in step 5.

Delete all the rows which caused the 1531 error from the original table:

1> delete <table_name> from #<temp_table_name> 
2> where <table_name.col1> = #<temp_table_name.col1> 
3> and <table_name.col2> = #<temp_table_name.col2> 
4> and <table_name.col3> = #<temp_table_name.col3> 
5> go

Now each row in the temporary table should have unique values for the columns in question. Add these rows back into the original table:
```
1> insert into <table_name> 
2> select * from #<temp_table_name> 
3> go
```
Create the index with the sorted_data option.

Additional information

Refer to “create index” in the Reference Manual: Commands for information about indexes.

Versions in which this error is raised

All versions