I have a table and, somehow, the same person got into my Person
table twice. Right now, the primary key is just an autonumber but there are two other fields that exist that I want to force to be unique.
For example, the fields are:
ID
Name
Active
PersonNumber
I only want 1 record with a unique PersonNumber and Active = 1.
(So the combination of the two fields needs to be unique)
What is the best way on an existing table in SQL server I can make it so if anyone else does an insert with the same value as an existing value, it fails so I don't have to worry about this in my application code.
This question is related to
sql
sql-server
In my case, I needed to allow many inactives and only one combination of two keys active, like this:
UUL_USR_IDF UUL_UND_IDF UUL_ATUAL
137 18 0
137 19 0
137 20 1
137 21 0
This seems to work:
CREATE UNIQUE NONCLUSTERED INDEX UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL
ON USER_UND(UUL_USR_IDF, UUL_ATUAL)
WHERE UUL_ATUAL = 1;
Here are my test cases:
SELECT * FROM USER_UND WHERE UUL_USR_IDF = 137
insert into USER_UND values (137, 22, 1) --I CAN NOT => Cannot insert duplicate key row in object 'dbo.USER_UND' with unique index 'UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL'. The duplicate key value is (137, 1).
insert into USER_UND values (137, 23, 0) --I CAN
insert into USER_UND values (137, 24, 0) --I CAN
DELETE FROM USER_UND WHERE UUL_USR_ID = 137
insert into USER_UND values (137, 22, 1) --I CAN
insert into USER_UND values (137, 27, 1) --I CAN NOT => Cannot insert duplicate key row in object 'dbo.USER_UND' with unique index 'UQ_USR_UND_UUL_USR_IDF_UUL_ATUAL'. The duplicate key value is (137, 1).
insert into USER_UND values (137, 28, 0) --I CAN
insert into USER_UND values (137, 29, 0) --I CAN
Source: Stackoverflow.com