how Unique key works on multiple columns

Question

i have a table T with following columns

col1 col2 col3 col4
1     1   1     1
1     1   1     2
1     1   2     1
1     1   2     1

if i set a column col2,col3,col4 as unique. how does the unique works ? will it take uniqueness of combination of each column?

score 1 · Answer 1 · answered Jan 18 '14 at 05:44

1

See here: http://www.w3schools.com/sql/sql_unique.asp

The syntax for setting multiple columns as unique is different from that of setting one column unique. If you have multiple columns as unique it is the set that is viewed for uniqueness.

answered Jan 18 '14 at 05:44

akronymn

2,426
4
25
39

score 1 · Answer 2 · edited May 23 '17 at 12:29

1

Yes, the "unique-ness" is a result of all columns involved in the constraint. See SO Question You can easily write yourself a table and test how it handles INSERTs

edited May 23 '17 at 12:29

Community

1
1

answered Jan 18 '14 at 05:57

MarkD

5,276
1
14
22

score 0 · Answer 3 · answered Jan 18 '14 at 05:46

I'm not entirely sure, but I think the unique attribute has to do with indexing the table. Whichever column you set as unique, that column should be the one you call on to find a certain row. For example in a call like

UPDATE table_name SET column_name = some_value WHERE ID = some_number

the ID column should be set to unique, though I don't know whether not doing so would actually stop you from finding a specific row.

how Unique key works on multiple columns

3 Answers3