Add unique constraint to combination of two columns

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 can also be done in the GUI:

1. Under the table "Person", right click *Indexes*
2. Click/hover *New Index*
3. Click *Non-Clustered Index...*

[![enter image description here][1]][1]

4. A default *Index name* will be given but you may want to change it.
5. Check *Unique* checkbox
6. Click *Add...* button

[![enter image description here][2]][2]

7. Check the columns you want included

[![enter image description here][3]][3]

8. Click *OK* in each window.


[1]:

[2]:

[3]:

And if you have lot insert queries but not wanna ger a ERROR message everytime , you can do it:


CREATE UNIQUE NONCLUSTERED INDEX SK01 ON dbo.Person(ID,Name,Active,PersonNumber)
WITH(IGNORE_DUP_KEY = ON)

[![enter image description here][1]][1]


[1]:

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

Once you have removed your duplicate(s):

ALTER TABLE dbo.yourtablename
ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);

or

CREATE UNIQUE INDEX uq_yourtablename
ON dbo.yourtablename(column1, column2);

Of course, it can often be better to check for this violation first, before just letting SQL Server try to insert the row and returning an exception (exceptions are expensive).

- [Performance impact of different error handling techniques](

[To see links please register here]

)

- [Checking for potential constraint violations before entering TRY/CATCH](

[To see links please register here]

)

If you want to prevent exceptions from bubbling up to the application, without making changes to the application, you can use an `INSTEAD OF` trigger:

CREATE TRIGGER dbo.BlockDuplicatesYourTable
ON dbo.YourTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted AS i
INNER JOIN dbo.YourTable AS t
ON i.column1 = t.column1
AND i.column2 = t.column2
)
BEGIN
INSERT dbo.YourTable(column1, column2, ...)
SELECT column1, column2, ... FROM inserted;
END
ELSE
BEGIN
PRINT 'Did nothing.';
END
END
GO

But if you don't tell the user they didn't perform the insert, they're going to wonder why the data isn't there and no exception was reported.

----

**EDIT** here is an example that does exactly what you're asking for, even using the same names as your question, and proves it. You should try it out before assuming the above ideas only treat one column or the other as opposed to the combination...

USE tempdb;
GO

CREATE TABLE dbo.Person
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(32),
Active BIT,
PersonNumber INT
);
GO

ALTER TABLE dbo.Person
ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
VALUES(N'foo', 1, 22);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
VALUES(N'foo', 0, 22);
GO

-- fails:
INSERT dbo.Person(Name, Active, PersonNumber)
VALUES(N'foo', 1, 22);
GO

Data in the table after all of this:

ID Name Active PersonNumber
---- ------ ------ ------------
1 foo 1 22
2 foo 0 22

Error message on the last insert:

> Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'uq_Person'. Cannot insert duplicate key in object 'dbo.Person'.
The statement has been terminated.

Also I blogged more recently about a solution to applying a unique constraint to two columns _in either order_:

- [Enforce a Unique Constraint Where Order Does Not Matter](

[To see links please register here]

)