1

I have a table: Sales_Table with columns:

ProductKey Int not null,
UnitPrice decimal 18,2,
SalesAmount decimal 18,2

I am trying to add a primary key to the ProductKey column using alter table 

ALTER TABLE SALES_TABLE
ADD PRIMARY KEY (ProductKey)

after I run the code and I get the following error:

Msg 1505, Level 16, State 1, Line 9 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Sales_Table' and the index name 'PK__Sales_Ta__A15E99B36F3FE24F'. The duplicate key value is (604).

What can I do to overcome this issue? Thanks in Advance.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Fahad Taher
  • 15
  • 2
  • 5
  • 1
    Primary keys have to be unique, but SQL Server is informing you that you have more than one row with the `ProductKey` value 604 in your table, so it can't create the primary key. – Diado Jun 21 '18 at 10:40
  • 1
    I'd hazard a guess that if you have 1 duplicate key, you probably have many of them; so you may well need to look much further than just `ProductKey` `604` – Thom A Jun 21 '18 at 10:42
  • 2
    Running `SELECT ProductKey, Count(*) FROM Sales_Table GROUP BY ProductKey HAVING COUNT(*) > 1` will give you a list of all ProductKeys that have duplicates – Diado Jun 21 '18 at 10:44
  • Thanks, I figured it was the duplicates in the ProductKey that was causing the error. Thanks for the help! – Fahad Taher Jun 21 '18 at 10:51
  • 4
    Possible duplicate of [Unable to create index because of duplicate that doesn't exist?](https://stackoverflow.com/questions/2180842/unable-to-create-index-because-of-duplicate-that-doesnt-exist) – Igor Cova Jun 21 '18 at 11:07

1 Answers1

0

Try this

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Product')
BEGIN
CREATE TABLE [dbo].[Product](
    [ProductKey] [int] NOT NULL,
    [UnitPrice] [decimal](18, 2) NULL,
    [SalesAmount] [decimal](18, 2) NULL,
) ON [PRIMARY]
END

IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='Pk_Product')
BEGIN
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [Pk_Product] PRIMARY KEY CLUSTERED 
(
    [ProductKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
Sreenu131
  • 2,476
  • 1
  • 7
  • 18