Allowing only unique combination in sql table

Question

I'm trying to create a sql table in net frame work in VS that has room and user id ,but I want that only one of each combination can exist in the table:

Want:
room --- user id
1           2
1           3
3           2
2           1
1           1
3           1
 
Dont want:
room --- user id
1           2
1           2

how can I make it so that only unique combination can be entered?

Does this answer your question? [Unique constraint on multiple columns](https://stackoverflow.com/questions/3474291/unique-constraint-on-multiple-columns) — Connor Low, Mar 11 '21 at 16:00

Gordon Linoff · Answer 1 · 2021-03-11T14:00:42.393

2

You can enforce uniqueness using a unique constraint or index:

create unique index unq_t_room_userid on t(room, user_id);

or:

alter table t add constraint unq_t_room_userid
    unique (room, user_id);

edited Mar 11 '21 at 14:00

answered Mar 11 '21 at 13:59

Gordon Linoff

1,198,228
53
572
709

if i enter this line and i try to add a non unique combination it wont let me? – RatLord122 Mar 11 '21 at 14:00
@RatLord122 . . . You will get an error if you attempt to add a row that violates the constraint or index. – Gordon Linoff Mar 11 '21 at 14:01

Metaphor · Answer 2 · 2021-03-11T14:29:15.937

Looks like you want Room, UserId to be the PRIMARY KEY on that table.

CREATE TABLE Occupancy
(
    RoomId INT NOT NULL
    ,UserId INT NOT NULL
    ,CONSTRAINT PK_Occupancy PRIMARY KEY CLUSTERED (RoomId,UserId)
)

When you try to insert a duplicate value:

INSERT dbo.Occupancy (RoomId,UserId)
VALUES
(1,1)
,(1,2)
,(1,1)
GO

You will get a message like this:

Violation of PRIMARY KEY constraint 'PK_Occupancy'. Cannot insert duplicate key in object 'dbo.Occupancy'. The duplicate key value is (1, 1).

Allowing only unique combination in sql table

2 Answers2