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

Non-NULL Unique Constraints on NULLable Columns

It is often desirable to enforce uniqueness on a column while allowing null values. However, creating a unique index on a NULLable column in SQL Server 2005 can be challenging.

Current Solutions

One common workaround is to create a unique index on a materialized view that excludes null values:

CREATE VIEW vw_unq WITH SCHEMABINDING AS
    SELECT Column1
      FROM MyTable
     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

Better Alternatives

SQL Server 2008 introduced filtered indexes, which provide a better solution:

CREATE UNIQUE INDEX AK_MyTable_Column1 ON MyTable (Column1) WHERE Column1 IS NOT NULL

This index allows null values while enforcing uniqueness on non-null values.

Additional Option: Triggers

Another alternative is to create a trigger to check for uniqueness during insert and update operations:

CREATE TRIGGER trg_MyTable_CheckUnique ON MyTable AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS(SELECT * FROM MyTable WHERE Column1 = NEW.Column1 AND Column1 IS NOT NULL)
    BEGIN
        RAISERROR('Duplicate value for Column1.', 16, 1)
    END
END

However, triggers can impact performance, especially on high-concurrency systems.

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