Error 1505

Severity

14

Message text

Create unique index aborted on duplicate key. Primary key is '%S_KEY'. 

Explanation

This error occurs when you try to create a unique index on a column or columns containing at least two rows with the same index value. Each time a unique index is created, Adaptive Server checks for duplicate index values (if data already exists), and displays this message if it finds any.

When this error occurs, no index is created.

A command of the following type generates a 1505 error if there are duplicate values on col1, col2, and col3:

create unique index <index_name> 
on <table_name>(<col1>, <col2>, <col3>)

Action

Decide whether you want to allow or prevent duplicate keys in the table where the index will be created. Use one of the following procedures:

• If you decide to allow duplicate keys, do not use the keyword unique when you create the index.

• If you decide to prevent duplicate keys, delete rows that have duplicate index values.

If you choose to delete duplicate keys, use the following procedure. For a brief period of time some rows from the table exist only in a temporary table. If a power failure or other interruption occurs, you could lose the temporary table and the rows in it. Therefore, before you begin this procedure, make a backup copy of the table or modify the procedure to use a table you create for this purpose instead of a temporary table.

1. Find the rows that caused the error:

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

   The query result contains all the rows causing the 1505 error.

2. 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
   

3. Some of the rows may contain identical values in the column or columns to be indexed and different values in other columns. Use the query below 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 4.

4. Delete all the rows which caused the 1505 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
   

5. 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
   

6. Now you can create the unique index on the table.

Additional information

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

Versions in which this error is raised

All versions