Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Duplicate Key Error on Primary Key Columns

By: FYIcenter.com

(Continued from previous topic...)

What Happens If You Insert a Duplicate Key for the Primary Key Column?

If your table has a primary key column, and you are trying to insert a new row with duplicate key value on the primary key column, you will get an error. The reason is simple - Primary key column does not allow duplicate values. The following tutorial exercise gives you a good example:

SELECT * FROM fyi_links
INSERT INTO fyi_links VALUES (101, 
  'sql.fyicenter.com', 
  NULL,
  0,
  '2006-04-30')
GO
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 
   'PK__fyi_links__03317E3D'. Cannot insert duplicate 
   key in object 'dbo.fyi_links'.
The statement has been terminated.

You are getting this error, because value "101" has already been used by an existing row.

(Continued on next topic...)

1. What Are DML (Data Manipulation Language) Statements?
2. How To Create a Testing Table with Test Data?
3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
4. How To Use Column Default Values in INSERT Statements?
5. How to provide column names in INSERT Statements?
6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
7. How To Insert Multiple Rows with One INSERT Statement?
8. How To Update Values in a Table with UPDATE Statements?
9. How To Update Multiple Rows with One UPDATE Statement?
10. How to use old values to define new values in UPDATE statements?
11. Is the Order of Columns in the SET Clause Important?
12. How To Use Values from Other Tables in UPDATE Statements?
13. What Happens If the UPDATE Subquery Returns No Rows?
14. What Happens If the UPDATE Subquery Returns Multiple Rows?
15. How To Delete an Existing Row with DELETE Statements?
16. How To Delete Multiple Rows with One DELETE Statement?
17. How To Delete All Rows with DELETE Statements?
18. How To Delete All Rows with TRUNCATE TABLE Statement?