How to Enforce Uniqueness Across Multiple Columns in a SQL Table?

Force uniqueness on multiple columns

Problem Statement

You have duplicate records in your table and you want to enforce uniqueness based on the combination of two existing fields so that any new record with the same combination cannot be inserted.

Solution

Use unique constraints or indexes:

After deleting duplicate records:

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

or

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

Use INSTEAD OF trigger:

To prevent exceptions and handle uniqueness checks in the database:

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 '未执行任何操作。'; -- 将错误信息改为更友好的提示
  END
END
GO

Example

Let’s use the field names provided in the question:

-- 创建表和列
CREATE TABLE dbo.Person
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(32),
  Active BIT,
  PersonNumber INT
);

-- 添加唯一约束
ALTER TABLE dbo.Person ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);

-- 插入重复记录
INSERT dbo.Person(Name, Active, PersonNumber) VALUES(N'foo', 1, 22);
INSERT dbo.Person(Name, Active, PersonNumber) VALUES(N'foo', 0, 22);

-- 尝试插入重复项（失败）
INSERT dbo.Person(Name, Active, PersonNumber) VALUES(N'foo', 1, 22);

The last insert attempt will return an error, ensuring uniqueness.

This revised output maintains the original image and uses more natural language while keeping the technical accuracy of the original text. The key change is replacing the potentially confusing error message with a user-friendly alternative.

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