How Does SQL (In my case SQL Server) Evaluate a Unique Constraint On Multiple Columns?

Question

Given the following table

CREATE TABLE example (
    id int NOT NULL IDENTITY(1,1),
    strA NVARCHAR(10) NULL,
    strB NVARCHAR(10) NULL,
  CONSTRAINT [pk_id] PRIMARY KEY (id)
  CONSTRAINT [unq_str_combo] UNIQUE (strA, strB)
  CONSTRAINT [chk_one_is_null] CHECK ((strA IS NULL AND strB IS NOT NULL) OR (strA IS NOT NULL AND strB IS NULL))
);

How does SQL decide if a combination of values in the columns strA and strB are a unique combo?

Should any of the following INSERT statements fail?

INSERT INTO dbo.example (strA, strB) VALUES ('abc', NULL);    -- A
INSERT INTO dbo.example (strA, strB) VALUES (NULL, 'def');    -- B
INSERT INTO dbo.example (strA, strB) VALUES (NULL, 'abc');    -- C
INSERT INTO dbo.example (strA, strB) VALUES ('def', NULL);    -- D

Is there a way during table creation (or by ALTER TABLE) to add a check or some other constraint such that the INSERT statements for C and D fail?

Answer 1

How does SQL decide if a combination of values in the columns strA and strB are a unique combo?

The two columns are compared separately. Uniqueness is violated on (strA, strB) if for two different rows both strA values compare equal and both strB values compare equal.

In SQL Server, NULL compares equal to another NULL when checking unique indexes and constraints. This is not the NULL behaviour prescribed by the SQL Standard, but it's not going to change.

Is there a way during table creation (or by ALTER TABLE) to add a check or some other constraint such that the INSERT statements for C and D fail?

This can be done with a unique constraint on a computed column:

CREATE TABLE example 
(
    id int NOT NULL IDENTITY(1,1),
    strA nvarchar(10) NULL,
    strB nvarchar(10) NULL,
    string AS COALESCE(strA, strB) 
        CONSTRAINT [unique strA strB] UNIQUE,

    CONSTRAINT [pk_id] PRIMARY KEY (id),
    CONSTRAINT [chk_one_is_null] CHECK (
        (strA IS NULL AND strB IS NOT NULL) 
        OR (strA IS NOT NULL AND strB IS NULL))
);

Answer 2

One way to implement a unique constraint on the mutually-exclusive StrA and StrB columns is with a unique index on a computed column. The example below uses this method instead of the composite column unique constraint in your question.

CREATE TABLE example (
    id int NOT NULL IDENTITY(1,1),
    strA NVARCHAR(10) NULL,
    strB NVARCHAR(10) NULL,
    str_combo AS COALESCE(strA,strB)
  CONSTRAINT [pk_id] PRIMARY KEY (id)
  ,CONSTRAINT [unq_str_combo] UNIQUE (str_combo)
  ,CONSTRAINT [chk_one_is_null] CHECK ((strA IS NULL AND strB IS NOT NULL) OR (strA IS NOT NULL AND strB IS NULL))
);

INSERT INTO dbo.example (strA, strB) VALUES ('abc', NULL);    -- A
INSERT INTO dbo.example (strA, strB) VALUES (NULL, 'def');    -- B
INSERT INTO dbo.example (strA, strB) VALUES (NULL, 'abc');    -- C
INSERT INTO dbo.example (strA, strB) VALUES ('def', NULL);    -- D

Results:

(1 row affected)

(1 row affected)
Msg 2627, Level 14, State 1, Line 14
Violation of UNIQUE KEY constraint 'unq_str_combo'. Cannot insert duplicate key in object 'dbo.example'. The duplicate key value is (abc).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 15
Violation of UNIQUE KEY constraint 'unq_str_combo'. Cannot insert duplicate key in object 'dbo.example'. The duplicate key value is (def).
The statement has been terminated.

Answer 3

As I was writing this up, I realized I should just run a test using the exact SQL code I included as an example. I was not able to find an answer to my corollary question yet (can I force C and D to fail?), but I felt it was worth posting the question and my research results for Posterity's sake.

Creating the Table and executing the Insert statements reveals that (at least in Sql Server 2017) values all of the Insert statements will succeed. This means that a value in column [strA] may have a duplicate value in column [strB].

Apparently, SQL server does not combine the two columns into a single value and trim any NULL values off when it does so. This means that for the unique constraint's purposes, when INSERT statement C is executed, SQL server treats the row resulting from statement A as abcNULL and the value you are inserting via C as something akin to NULLabc.

Please understand that this is likely an extreme over simplification, and perhaps it would be better to consider the values to be NULL, 'abc' and 'abc', NULL to distinguish that the duplicates are in different columns.