CODEWITHSUNDEEP
sqlunique
user6105633
user6105633

Reputation:

SQL set UNIQUE only for two columns

I would like for example that user can add name JHONE and age 25, so next time he can add JHONE, 26 or ALEX 25, BUT not JHONE, 25 again.

So I'm looking for two column unique NOT separately.

P.S. I'm sorry if same question was mentioned before.

EDIT:

This is my example:

enter image description here

Would like to make userIdG and doWithCar will be like this

102163096246025413003 View

102163096246025413003 Buy

102163096246025413003 Let

102163096246025413003 Sell

And for Id = 102163096246025413003 you can't add any more values, BECAUSE column doWithCar will have only 4 possible choice view, buy, rent and sell

Upvotes: 1

Views: 36

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176274

You could specify more than one column in UNIQUE:

CREATE TABLE tab(ID INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(100), age INT
                ,UNIQUE(name, age));

INSERT INTO tab(name, age) VALUES ('John', 25);
INSERT INTO tab(name, age) VALUES ('John', 26);                 

-- INSERT INTO tab(name,age) VALUES ('John', 25);
-- Violation of UNIQUE KEY constraint 'UQ__tab__CF0426FD76D3370A'. 
-- Cannot insert duplicate key in object 'dbo.tab'. 
-- The duplicate key value is (John, 25).
-- The statement has been terminated.

SELECT * FROM tab;

LiveDemo

Note:

You should store date of birth and not age itself (or make age calculated column and set UNIQUE(name, dob)).

this is what I do not understand) how database will know that it should be two columns as unique and not each column is unique

These are different concepts. DB "knows" it from UNIQUE constraint definition:

UNIQUE(userIdG,doWithCar)              -- pair of column is unique
!=
UNIQUE(userIdG),UNIQUE(doWithCar)      -- each column is unique

Upvotes: 2

Related Questions