What is the best way prevent duplicate records in a SQL Server database

Question

what is the best way to prevent duplicate records in a SQL Server database? Using triggers? Using a unique constraint?

As a *general rule*, I'd recommend using features which are designed and dedicated to a particular usage in preference to using triggers. Only use triggers if the other DRI features aren't able to achieve your overall goals. — Damien_The_Unbeliever, May 05 '15 at 10:35
Why was this downvoted? It seems like a fair question to me. I *did* Google it, and this post came up. That's good for Stack Overflow, right? — EJ Mak, Oct 22 '15 at 15:27
@Shana: If an answer was helpful, please upvote or mark it as answered. — EJ Mak, Oct 22 '15 at 15:28

score 9 · Answer 1 · edited Jun 27 '20 at 18:38

Use unique constraints on one or more columns in the table. Example:

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL UNIQUE,
FirstName varchar(255) NOT NULL UNIQUE, 
Address varchar(255),
City varchar(255)
)

Alter existing table as below

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

If you are using a front-end application to populate the table in the database. Do your validation select query from the application on the database to check for duplicates before inserting into the database. Using constraints will prevent duplicates by throwing an exception.

Note: The above example is SQL SERVER, Oracle, ms access

For much more indepth solution see How to prevent duplicate records being inserted with SqlBulkCopy when there is no primary key

@Chukwu can you please give me example. Thanks – Shana May 05 '15 at 10:35 — Shana, May 05 '15 at 10:35

score 1 · Answer 2 · answered May 05 '15 at 11:49

If you don't want error throw from unique constraint and you also want database to receive duplicated data but insert nothing. You may look at merge statement

https://technet.microsoft.com/en-us/library/bb522522%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

What is the best way prevent duplicate records in a SQL Server database

2 Answers2