CODEWITHSUNDEEP
sql-serverprimary-key
mgrenier
mgrenier

Reputation: 1447

SQL Unique Key Violation

I am getting Unique Key violation and I am not sure why, here is my scenario. I have a table with the following fields:

ID, LocationID, Name, Description, Dewey

The ID is obviously the primary key, LocationID is a foreign key linked to the Location table and the other fields are simply varchar fields with no relationship associated with them.

When I insert a row where the LocationID and Name have the same value as another row in the table I get this error:

Violation of UNIQUE KEY constraint 'IX_InternalLocation'. Cannot insert duplicate key in object 'dbo.InternalLocation'. The statement has been terminated.

If either of those fields is different I can insert the record no problem. This seems weird to me as neither of these rows is the unique identifier for the table.

It appears for some reason the DB thinks together the fields form a key but that is not how it should be and when I look at the table properties that is not how it is. Any help would be greatly appreciated.

Upvotes: 0

Views: 2022

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Run this query:

SELECT tc.Constraint_Name, cc.Column_Name 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc 
   ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_NAME = 'IX_InternalLocation' AND tc.CONSTRAINT_TYPE = 'Unique'

against your database.

You should get as result:

Constraint_Name                  Column_Name
----------------------------------------------------- 
IX_InternalLocationDateMexri     LocationID 
IX_InternalLocationYpallhlosId   Name

which means there is a UNIQUE INDEX placed on those two columns, prohibiting any duplicate pair insertion.

Upvotes: 3

Related Questions