J.FOG J.FOG - 7 months ago 8
SQL Question

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

Answer

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 constaint definition:

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