How to Enforce a Unique Constraint Across Multiple Columns in SQL Server?

Enforcing Uniqueness Across Multiple SQL Server Columns

Often, database integrity requires preventing duplicate rows based on multiple column values. This article demonstrates how to enforce a unique constraint across the PersonNumber and Active columns in a Person table.

Implementing the Unique Constraint

After removing any pre-existing duplicate entries, apply the constraint using one of these methods:

• ALTER TABLE Statement: This directly adds the constraint to the existing table.

  <code class="language-sql">ALTER TABLE dbo.Person ADD CONSTRAINT uq_Person_NumberActive UNIQUE (PersonNumber, Active);</code>

• CREATE UNIQUE INDEX Statement: This creates a unique index, which implicitly enforces the uniqueness constraint.

  <code class="language-sql">CREATE UNIQUE INDEX uq_Person_NumberActive ON dbo.Person (PersonNumber, Active);</code>

Alternative Approaches & Optimization

While the above methods are effective, consider these enhancements:

• Performance: Proactive duplicate checks before attempting an insert can improve performance by avoiding exceptions within TRY...CATCH blocks.
• INSTEAD OF Trigger: An INSTEAD OF trigger allows for conditional insert logic, preventing exceptions from propagating to the application layer. This offers a more robust error-handling mechanism.

Illustrative Example

This example demonstrates the unique constraint in action:

<code class="language-sql">-- Create the Person table
CREATE TABLE dbo.Person (
  ID INT IDENTITY(1, 1) PRIMARY KEY,
  Name NVARCHAR(32) NOT NULL,
  Active BIT NOT NULL DEFAULT 0,
  PersonNumber INT NOT NULL
);

-- Add the unique constraint
ALTER TABLE dbo.Person ADD CONSTRAINT uq_Person_NumberActive UNIQUE (PersonNumber, Active);

-- Insert data
INSERT INTO dbo.Person (Name, Active, PersonNumber) VALUES ('John Doe', 1, 1234);
INSERT INTO dbo.Person (Name, Active, PersonNumber) VALUES ('Jane Doe', 0, 5678);

-- Attempt to insert a duplicate (this will fail)
INSERT INTO dbo.Person (Name, Active, PersonNumber) VALUES ('John Doe', 1, 1234);</code>

This ensures that only one record exists for each unique combination of PersonNumber and Active status. Any attempt to insert a duplicate will result in an error. Remember to replace dbo.Person with your actual schema and table name.

The above is the detailed content of How to Enforce a Unique Constraint Across Multiple Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!