CODEWITHSUNDEEP
sqlsql-serversql-insert
S3minaki
S3minaki

Reputation: 319

Violation of UNIQUE KEY constraint (Error msg:2627)

I use sql server an i try to run the query below:

declare @ProductID int
set @ProductID=322

INSERT INTO table_Customer (ProductID,Type,CompanyID) 
SELECT @ProductID,' ',CompanyID 
FROM table_Companies 
WHERE CompanyActive=1

And i get the error below:

Msg 2627, Level 14, State 1, Line 19

Violation of UNIQUE KEY constraint 'IX_table_Customer'. Cannot insert duplicate key in object 'dbo.table_Customer'. The duplicate key value is (322, , , , , , ).

Find below table's definition.

> CREATE TABLE [dbo].table_Customer(

> [CustomerID] [int] IDENTITY(1,1) NOT NULL,
> 
> [ProductID] [int] NOT NULL,

> [Type] [nvarchar](5) NULL,
> 
> [CompanyID] [int] NULL,
> 
> [RoleID] [int] NULL,
> 
> 

>  CONSTRAINT [PK_table_Customer] PRIMARY KEY CLUSTERED  (
> 
> [CustomerID] ASC
> 
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF,
> 
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> [PRIMARY],
> 
>  CONSTRAINT [IX_table_Customer] UNIQUE NONCLUSTERED  (

>   [ProductID] ASC,
> 
>   [CompID] ASC,
> 
>   [CompManager] ASC
> 
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF,
> 
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> [PRIMARY]
> 
> ) ON [PRIMARY]

Can someone help me?

Upvotes: 1

Views: 11628

Answers (4)

Mr.No
Mr.No

Reputation: 83

ProductID is probably identity. if it is really a must. Remove it and add it back when finished. But clearly you already have data for id 322

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

The query you used will repeat the same @ProductID value for every active company in the table_Companies table. 

SELECT @ProductID,' ',CompanyID 
FROM table_Companies 
WHERE CompanyActive=1

If the index contains the ProductID column only, or at least it doesn't contain the CompanyID field as well, this will result in duplicate ProductID entries.

Upvotes: 2

Peter B
Peter B

Reputation: 24147

You are possibly inserting multiple rows as they are being returned from the SELECT:

INSERT INTO table_Customer (ProductID,Type,CompanyID)
SELECT @ProductID, ' ', CompanyID
FROM   table_Companies
WHERE  CompanyActive = 1  -- How many rows does this produce...?

Note that @ProductID is a FIXED value in this query, so it will be the same (322) for all selected-and-then-inserted rows.

Your index is complaining that you can't insert rows having the same set of index values. So either there is already a record with those values, or the SELECT produces duplicates, or both of these combined.

Upvotes: 2

Dcarr
Dcarr

Reputation: 55

It's saying that you already have a a unique key of 322 in the customer table

Select * from table_Customer where productID = 322;

Upvotes: 0

Related Questions