Search code examples
sqlsql-serverdatabase-designduplicateshaving-clause

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found

This is unfinished database for selling train tickets. enter image description here I want to create primary key for RouteId in Route table, but i got an exception:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Route' and the index name 'PK_Route'. The duplicate key value is (1).

But there are no another key.

I think the problem may be that initially I had 2 tables Route and RouteStation, than I delete table Route and rename RouteStation to Route.

Another themes on this site does not help me. I also tried to see key for this table, but output was empty:

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = 'Route'
Solution

  • The problem is with the content of the route table. The message tells you that you have duplicate values in columns that are referenced by constraint PK_Route - probably RouteId. You can exhibit them with:

    select RouteId from Route group by RouteId having count(*) > 1