An interesting workaround using unique fields
Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index.
Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.
The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.
First, let’s see the regular behavior of unique fields:
(
id INT IDENTITY(1, 1),
value VARCHAR(30) NULL UNIQUE
)
go
— The first two inserts will work
INSERT INTO testunique
VALUES (‘value1’)
INSERT INTO testunique
VALUES (NULL)
go
–Next two inserts will fail, they aren’t unique
INSERT INTO testunique
VALUES (‘value1’)
go
INSERT INTO testunique
VALUES (NULL)
go
DROP TABLE testunique
Now let reapeat the same example again, using a filtered index:
(
id INT IDENTITY(1, 1),
value VARCHAR(30) NULL
)
go
— Creating the filtered index
CREATE UNIQUE INDEX indunique
ON testunique(value)
WHERE value IS NOT NULL
go
— The first two inserts will work
INSERT INTO testunique
VALUES (‘value1’)
INSERT INTO testunique
VALUES (NULL)
go
–This insert will fail, the first value is already present
INSERT INTO testunique
VALUES (‘value1’)
go
— this second insert will work, null values are not part
— of the index, therefore duplicate values are accepted
INSERT INTO testunique
VALUES (NULL)
go
DROP TABLE testunique
Load comments