Error 2615

Severity

14

Message text

Attempt to insert duplicate row in table '%.*s' with index '%.*s' in database '%.*s'. Could drop and recreate index with ignore duprow or allow duprow.

Explanation

Tables with clustered indexes do not allow duplicate rows. Error 2615 occurs:

NoteThe duplicate syskeys entries are not caused by the upgrade process; rather, the upgrade process reveals such entries if they exist.

Action


Case 1

If the duplicate values are a result of incorrect data, change the data and rerun the transaction. Otherwise, decide whether to allow or prevent duplicate rows in the table where the clustered index will be created. Use one of the following procedures.

Noteignore_dup_row and allow_dup_row are mutually exclusive options of the create index command. Do not use them together in the same statement, which will cause error 1916.


Allowing duplicate rows

Create the index with the allow_dup_row option:

1> create clustered index <index_name> on <table_name> 
2> with allow_dup_row
3> go

Subsequent update and insert commands can create duplicate rows after the allow_dup_row option is set.

WARNING! Do not use this procedure if you have a unique index on any column or columns in the table. Unique indexes are only used on tables where no duplicate rows exist.


Deleting duplicate rows

Create the index with the ignore_dup_row option:

1> create clustered index <index_name> on <table_name> 
2> with ignore_dup_row
3> go

Existing duplicate rows will be deleted from the table as the index is created. Subsequent attempts to enter a duplicate row with insert or update are ignored and the insert or update is cancelled with an informational message. If the insert or update is part of a larger transaction, the rest of the transaction will continue as usual.


Case 2

If the error occurs during upgrade, begin by making sure that you are upgrading using one of the recommended migration paths:

If you do not upgrade using one of these paths, error 2615 may be raised during a required upgrade step and that the error will abort the upgrade. By following the recommended path, you may encounter the error, but it is benign and will not cause the upgrade to abort.

After the upgrade completes, use the following steps after the upgrade to correct the problem by removing duplicate syskeys rows:

  1. Identify duplicate rows.

    Execute the following query in the database named in the 2615 error to identify the duplicate syskeys entries:

    1> select id,
    
    2>'Primary object' = object_name(id), 
    3> type, 
    4> depid,
    5> 'Dependent object' = object_name(isnull(depid,0)),
    6> spare1 
    7> from syskeys k1
    8> where exists (select spare1 
    9> from syskeys k2
    10> where k1.id = k2.id 
    11> and k1.type = k2.type
    12> and k1.spare1 != k2.spare1)
    13> go
    

    Identify the duplicate syskeys entries that have two identical (id, type) pairs.

    To see if there are more than two entries with identical (id, type) pairs, execute this query:

    1> select * from syskeys 
    2> where id = <test_id> and type = <test_type>
    3> go
    

    The duplicates are the rows where ALL columns except spare1 match.

  2. Eliminate duplicate syskeys entries.

    1. Allow updates to system tables:

      1> sp_configure "allow updates", 1 
      2> go
      
    2. Delete all but one of each duplicate set. Repeat the following command once for each duplicate:

      1> delete syskeys
      2> where id = <duplicate_id>
      3> and type = <duplicate_type>
      4> and spare1 = <one_of_the_duplicates>
      5> go
      
    3. (Optional) Before disallowing updates to system tables (step d), you can execute the following commands to prevent other duplicates:

      1> update syskeys
      2> set spare1 = 0
      3> where spare1 != 0
      4> go
      
    4. After removing all duplicates, disallow updates to system tables:

      1> sp_configure "allow updates", 0
      2> go
      

Additional information

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

Versions in which this error is raised

All versions