How Can I Prevent Duplicate Database Records Using Unique Constraints and Triggers?

Use unique constraints to prevent duplicate records

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

<code>ID | Name | Active | PersonNumber</code>

You may need to enforce a unique constraint to prevent duplicates based on the combination of PersonNumber and Active = 1. This ensures that no two rows have the same PersonNumber and Active status.

Add unique constraint to existing table

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

1. ALTER TABLE using unique constraints

<code>ALTER TABLE dbo.yourtablename
ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);</code>

2. Create a unique index

<code>CREATE UNIQUE INDEX uq_yourtablename
ON dbo.yourtablename(column1, column2);</code>

Use triggers to prevent repeated insertions

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

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: Enforcing uniqueness on PersonNumber and Active

The following example shows how to add a unique constraint on the combination of PersonNumber and Active in the 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

Any insertion attempt that violates the unique constraint will now fail with an error message, ensuring there are no duplicate records with the same PersonNumber and Active status. The PRINT '未执行任何操作。' statement replaces the PRINT 'Did nothing.' in the original text, making it easier to understand.

The above is the detailed content of How Can I Prevent Duplicate Database Records Using Unique Constraints and Triggers?. For more information, please follow other related articles on the PHP Chinese website!