Thursday, June 30, 2011

How to prevent duplicates in 2 columns in one table

We know how to define column as unique, but what to do where you need to prevent duplicate in 2 columns?
This is a state:
CREATE TABLE #tmp
(
Id INT,
Wine VARCHAR(50),
Country VARCHAR(50)
)

INSERT INTO #tmp VALUES
(1,'Merlot','France')
INSERT INTO #tmp VALUES
(2,'Merlot','Argentina')
INSERT INTO #tmp VALUES
(3,'Malbec','Argentina')

SELECT * FROM #tmp
DROP TABLE #tmp

A result is:


Id |  Wine    |  Country
----------- ------------------
1 | Merlot | France
2 | Merlot | Argentina
3 | Malbec | Argentina


A target to prevent:



Id |  Wine    |  Country
----------- ------------------

1 | Merlot | France
2 | Merlot | Argentina
3 | Malbec | Argentina

4 | Merlot | France
5 | Caberne | France



That means table allow duplicate Wines and duplicate Countries, but must prevent double rows.
A solution is to add next row after table creation:
ALTER TABLE  #tmp      ADD UNIQUE(Wine, Country)



Enjoy


No comments: