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.

Solution 1:

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

• Checking for potential constraint violations before entering TRY/CATCH

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

Solution 2:

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...

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

7. Check the columns you want included

8. Click OK in each window.

Solution 3:

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