While unlikely, it is possible that the PK values are duplicate, yet the row as a whole is not. Each time it is run, set rowcount to n-1 the number of duplicates of the particular PK value.īefore deleting the rows, you should verify that the entire row is duplicate. If the GROUP BY query returns multiple rows, the "set rowcount" query will have to be run once for each of these rows. The col1/col2 values are taken from the above GROUP BY query result. In this example, there are 2 duplicates so rowcount is set to 1. The rowcount value should be n-1 the number of duplicates for a given key value. If there are only a few sets of duplicate PK values, the best procedure is to delete these manually on an individual basis. The last column in this result is the number of duplicates for the particular PK value. This will return one row for each set of duplicate PK values in the table. The first step is to identify which rows have duplicate primary key values: Insert into t1 values (1, 2, 'data value two') Insert into t1 values (1, 1, 'data value one')
This procedure illustrates how to identify and remove the duplicates.Ĭreate table t1(col1 int, col2 int, col3 char(50)) We cannot create a unique index or PRIMARY KEY constraint since two rows have duplicate PKs. In this table the primary key is the two columns (col1, col2). However, you should closely examine the process which allowed the duplicates to happen in order to prevent a recurrence.įor this example, we will use the following table with duplicate PK values. This article discusses how to locate and remove duplicate primary keys from a table. Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name '%.*ls' and index name '%.*ls'. Often duplicate PKs are noticed when you attempt to create a unique index, which will abort if duplicate keys are found.
Another way they can occur is through a database design error, such as not enforcing entity integrity on each table. One way they can occur is if duplicate PKs exist in non-relational data outside SQL Server, and the data is imported while PK uniqueness is not being enforced. SQL Server has various mechanisms for enforcing entity integrity, including indexes, UNIQUE constraints, PRIMARY KEY constraints, and triggers.ĭespite this, under unusual circumstances duplicate primary keys may occur, and if so they must be eliminated. For brevity, we will sometimes refer to primary keys as "key" or "PK" in this article, but this will always denote "primary key." Duplicate PKs are a violation of entity integrity, and should be disallowed in a relational system. Microsoft SQL Server tables should never contain duplicate rows, nor non-unique primary keys. This will return a list of the names of all triggers on the table.Microsoft SQL Server 2005 Standard Edition Microsoft SQL Server 2005 Express Edition Microsoft SQL Server 2005 Developer Edition Microsoft SQL Server 2005 Enterprise Edition Microsoft SQL Server 2005 Workgroup Edition More. In order to reliably find only triggers on a given table, you have to check the sysobjects table: 1> select so2.name from sysobjects so1, sysobjects so2 where (so2.id = so1.deltrig or so2.id = so1.instrig or so2.id=so1.updtrig or so2.id=so1.seltrig) and so1.name='tablename'
In order to find all triggers on a table (select, delete, insert and update triggers), you can use the stored procedure sp_depends: 1> sp_depends tablename