To avoid duplicate record in database, it is better to check for duplicate certain column data before insert. Primary key is a good mechanic to prevent duplicate row but don't count on it. Take a look the following SQL statements.
-- Creating temporary table (Notice the primary key is set to identity)
CREATE TABLE #tblTemp
( ID int identity primary key,
ColA nvarchar(30),
ColB nvarchar(50)
);
GO
-- Inserting new record into the temporary table tblTemp
-- If execute this insert statement twice, you will have two row have same data in ColA and ColB
-- with difference ID
INSERT INTO #tblTemp VALUES ('Test A1', 'Test B1');
-- This is better
IF NOT EXISTS (SELECT 1 FROM #tblTemp WHERE #tblTemp.ColA='Test A1')
INSERT INTO #tblTemp VALUES ('Test A1', 'Test B1')
SELECT * FROM #tblTemp
Another example using sample database AdventureWorks2012.
USE AdventureWorks2012;
GO
SELECT * FROM AdventureWorks2012.Person.Address WHERE AddressLine1='1970 Napa Ct.';
GO
-- We don't want a duplicate record for AddressLine1 '1970 Napa Ct.' and PostalCode 1597
IF NOT EXISTS (SELECT 1 FROM AdventureWorks2012.Person.Address WHERE AddressLine1='1970 Napa Ct.' AND PostalCode=1597)
INSERT INTO AdventureWorks2012.Person.Address (AddressLine1, City, StateProvinceID, PostalCode)
VALUES ('1970 Napa Ct.', 'Lane Cove', 50, 1597);
SELECT * FROM AdventureWorks2012.Person.Address WHERE AddressLine1='1970 Napa Ct.';
GO
