How to Enforce Unique Constraints on Multiple Columns in SQL Server?

The unique constraint of multiple columns in SQL Server

Make sure the unique combination of multiple columns is essential for maintaining data integrity. In SQL Server, you can achieve this purpose by achieving unique constraints or unique indexes on the specified columns.

The demand for the only constraint

The example provided by emphasizes the uniqueness of Personnumber and Active field combinations in the Person table. This can prevent multiple persons input multiple times, each of which Personnumber is allowed only one record.

Add unique constraints

To enforce this limit, you can use the following syntax to add unique constraints:

or, you can create a unique index:

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

Both methods have the same purpose, that is, repeated records are prohibited according to the combination of specified columns.

Maintain efficiency

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

When the unique constraint is implemented, it is necessary to weigh the performance impact. Although they ensure data integrity, if they often violate the constraints, they may also lead to expenses of insert operations. In this case, consider using the Instead of trigger to intercept and prevent the execution from invalid insertion.

Remember, if the trigger prevents the insertion operation, please provide feedback to the user to prevent potential uncertainty.

Example

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

The application of this solution to the examples provided will get the following table structure:

Now prohibited from inserting a repeated record based on the combination of Personnumber and Active, as shown in the following example:

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