8

I want to make three columns are unique in my data table 

-----------------------------------
 Column A  |  Column B | Column C
----------------------------------
  Kasun     Cham      Nimith   
----------------------------------
  Kasun     Cham      Rox      - This row ok and must be allowed to add.
----------------------------------
  Kasun     Cham      Nimith  -  but This row must not be allowed to add again, 
---------------------------------

how can I accomplish this in SQL server ?

  • 2
    make Columns A,B,C primary keys? – Reyno Jun 22 '13 at 10:38
  • @Reyno that will cause every PK column to have a unique entry per row. Wont work with the above case. – Chris Wijaya Jun 22 '13 at 10:50
  • 2
    @Chris'o - It will work fine. `CREATE TABLE X(A VARCHAR(10), B VARCHAR(10), C VARCHAR(10), PRIMARY KEY(A,B,C))` ensures that the **combination** of `a,b,c` is unique. – Martin Smith Jun 22 '13 at 10:54
  • Refer to composite primary key http://stackoverflow.com/questions/3922337/how-to-create-composite-primary-key-in-sql-server-2008 – Aditya Jun 22 '13 at 10:56

3 Answers3

8

You can add a unique constraint:

ALTER TABLE [TableName] ADD CONSTRAINT  [constraintName] UNIQUE ([columns])

You can read the documentation here.

jpw
  • 44,361
  • 6
  • 66
  • 86
5

This code will do that what you want.

CREATE UNIQUE CLUSTERED INDEX index_name ON TABLE (col1,col2, col3)
or
CREATE UNIQUE NONCLUSTERED INDEX index_name ON TABLE (col1,col2 , col3)

or

ALTER TABLE [dbo].[TABLE] ADD CONSTRAINT
UNIQUE_Table UNIQUE CLUSTERED
(
col1,
col2, 
col3
) ON [PRIMARY]
Kas
  • 3,747
  • 5
  • 29
  • 56
1

To handle that issue with this table design, you need to create a unique constraint on your columns A/B/C.

Ahmed
  • 452
  • 3
  • 7