2

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'
GMB
  • 216,147
  • 25
  • 84
  • 135
Roomey
  • 716
  • 2
  • 9
  • 16
  • Does your table already contain data? – GMB Mar 07 '20 at 16:36
  • ........ @GMB yes – Roomey Mar 07 '20 at 16:38
  • 1
    You cannot add a unique index on a column which does not contain duplicate values. – Jakob Busk Sørensen Mar 07 '20 at 16:39
  • 1
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged – marc_s Mar 07 '20 at 16:43
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. – philipxy Jul 16 '20 at 04:10
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] – philipxy Jul 16 '20 at 04:12

1 Answers1

9

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
GMB
  • 216,147
  • 25
  • 84
  • 135