Search code examples
sqlsql-server

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

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

    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:

    EDIT here is an example that does something I didn't think you asked for, but that a commenter 12 years later thinks you did. Let's say the problem was stated as:

    I'd like to allow any number of rows for a given PersonNumber with Active = 0, but only one row for that PersonNumber can have Active = 1.

    Ok, then it's not really a unique constraint as described:

    (So the combination of the two fields needs to be unique)

    And you can accomplish that with a not-quite-ANSI-complaint unique constraint, using a unique index with a filter:

    CREATE UNIQUE INDEX uq_ActivePerson
  ON dbo.Person(PersonNumber)
  WHERE Active = 1;

    So now, if your intention is to allow a PersonNumber to have 6000 rows where Active = 0, they can still only have one row where Active = 1. I still don't think that's what you were originally after in 2013, or it probably would have come up before or shortly after accepting. But since the intention of your question seems to be up for various interpretations...

    Now, a unique index and a unique constraint happen to be implemented the same way under the covers in SQL Server, so this is just semantics. But your documentation should explain why this is a constraint enforced via an index.