Complete Guide to Adding Unique Constraints on Column Combinations in SQL Server

Problem Background and Requirement Analysis

In database design, scenarios frequently arise where uniqueness of certain column combinations must be enforced. As described by the user, duplicate person records exist in the Person table. While the primary key ID is an auto-incrementing unique identifier, business logic requires that the combination of PersonNumber and Active=1 must be unique. This means only one active record is permitted per person number.

Core Solutions: Unique Constraints vs Unique Indexes

SQL Server offers two primary methods to enforce uniqueness on column combinations: unique constraints and unique indexes.

Unique Constraint Method

The most straightforward approach uses the ALTER TABLE statement to add a unique constraint:

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

Unique Index Method

An equivalent implementation involves creating a unique index:

CREATE UNIQUE INDEX uq_Person
  ON dbo.Person(PersonNumber, Active);

Comparison of Both Methods

Although functionally similar, these approaches differ in their implementation mechanisms. Unique constraints define data integrity rules at the conceptual level, while unique indexes achieve this through physical index structures. In practice, both effectively prevent duplicate data insertion.

Practical Case Demonstration

The following complete example demonstrates the actual effect of a unique constraint:

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

-- Successful insert: PersonNumber 22, Active status 1
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);

-- Successful insert: PersonNumber 22, Active status 0
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 0, 22);

-- Failed insert: PersonNumber 22, Active status 1 (duplicate combination)
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);

The execution results will show the first two records inserted successfully, while the third fails due to unique constraint violation, returning the error message: "Violation of UNIQUE KEY constraint 'uq_Person'. Cannot insert duplicate key in object 'dbo.Person'."

Performance Considerations and Exception Handling

While relying directly on database constraints ensures data integrity, exception handling requires performance consideration. Each constraint violation throws an exception, and exception handling is relatively expensive at the database level.

Pre-check Strategy

Pre-checking for potential conflicts in the application can avoid unnecessary exceptions:

IF NOT EXISTS (
    SELECT 1 FROM dbo.Person 
    WHERE PersonNumber = @PersonNumber AND Active = 1
)
BEGIN
    INSERT dbo.Person(Name, Active, PersonNumber)
    VALUES(@Name, @Active, @PersonNumber);
END

Advanced Solution: INSTEAD OF Triggers

For scenarios where application code modifications are undesirable, INSTEAD OF triggers can silently handle duplicate insertions:

CREATE TRIGGER dbo.BlockDuplicatesPerson
 ON dbo.Person
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 FROM inserted AS i 
    INNER JOIN dbo.Person AS t
    ON i.PersonNumber = t.PersonNumber
    AND i.Active = t.Active
  )
  BEGIN
    INSERT dbo.Person(Name, Active, PersonNumber)
      SELECT Name, Active, PersonNumber FROM inserted;
  END
  ELSE
  BEGIN
    -- Optional: Log the event or notify user
    PRINT 'Duplicate record insertion blocked';
  END
END

Supplementary Knowledge: Understanding Combination Uniqueness

The shopping cart example from the reference article further illustrates the concept of combination uniqueness. Under the combined constraint of cartID and productID, combinations like (10,7), (10,8), and (11,7) are all permitted, but re-inserting (11,7) is blocked. This confirms that combination constraints apply to the tuple of column values, not individual columns.

Best Practice Recommendations

When selecting an implementation approach, consider the following factors:

• Data Integrity Priority: For critical business data, prioritize database-level constraints
• Performance Balance: In high-concurrency scenarios, consider pre-check strategies to reduce exception overhead
• Maintenance Convenience: Trigger solutions suit legacy systems but increase maintenance complexity
• User Experience: Ensure users receive timely feedback on operation results, avoiding confusion from silent failures

Conclusion

Enforcing uniqueness on column combinations through unique constraints or indexes is a common requirement in database design. This article provides complete solutions ranging from basic implementation to advanced optimization, helping developers choose the most appropriate method for specific scenarios. Whether through simple constraint addition or complex trigger implementation, the core objective remains ensuring data consistency and integrity while balancing system performance and user experience.