Ensuring All Non-NULL Values Are Unique

Ensuring All Non-NULL Values Are Unique

Say you have a column in a SQL Server table that allows NULL values. You want this column to be unique when it has a value other than NULL. What’s the best way to program this behavior?

SQL Server has no built-in mechanism to prohibit duplicates exceptNULLs, so you need to implement a custom CHECK constraint to enforce this restriction. For example, the following code snippet enforces exactly this kind the kind of integrity:

 USE tempdbCREATE table t1 (c1 int NULL, c2 char(5) NULL)CREATE trigger mytrigger on t1 for insert, update asBEGIN       IF (select max(cnt) from (select count(i.c1)as cnt from t1, inserted i where t1.c1=i.c1 groupby i.c1) x) > 1       ROLLBACK TRANEND
See also  Why ChatGPT Is So Important Today

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist