Use unique constraints to prevent duplicate records

In database operations, it is often necessary to ensure the uniqueness of specific column combinations. For example, consider the following table:

ID | Name | Active | PersonNumber

You may need to enforce a unique constraint to prevent duplicates based on combinations of PersonNumber and Active = 1. This ensures that there are no two rows with the same PersonNumber and Active states.

Add unique constraints to existing tables

To add a unique constraint to an existing table, you can use one of the following methods:

1. ALTER TABLE

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

2. Create a unique index

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

Use triggers to prevent repeated insertion

If you want to prevent repeated insertions without relying on exceptions, you can use the INSTEAD OF trigger. This trigger intercepts insertion attempts and allows insertion of rows only if they do not violate unique constraints:

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: Implement uniqueness for PersonNumber and Active

The following example demonstrates how to add unique constraints to the combination of PersonNumber and Active in a Person table:

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

Now, any insertion attempt that violates the unique constraint will fail with an error message, ensuring that there is no duplicate record with the same PersonNumber and Active states. PRINT 'No action was performed. The ' statement replaces the PRINT 'Did nothing.' in the original text to make it easier to understand.