SQL Server: specifying a unique optional column

Question

SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table.

For example:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) unique  --  optional, but unique
);

(In Australia, we have a Tax File Number, which is unique to the tax payer).

The following is a workaround:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9)
);
CREATE UNIQUE NONCLUSTERED INDEX uq_employees_tfn ON employees(tfn)
WHERE tfn IS NOT NULL;

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Answer 1

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Yes, the following works (only in SQL Server 2016 onwards):

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) NULL 
        INDEX uq_employees_tfn 
        UNIQUE (tfn) 
        WHERE tfn IS NOT NULL
);

The inline syntax results in the same unique filtered index as for the separate CREATE UNIQUE INDEX. Subsequent DDL will need to manage it like an index rather than a constraint.

It is not brilliantly clear in the CREATE TABLE documentation, but is illustrated in Example S.

Answer 2

Paul White's answer is excellent for SQL Server versions and other RDBMS which support filtered indexes. For other database systems a good way to get around this would be to have a separate, child table to contain the unique, but optional column.

The tables would be in an optional 1:1 relationship (i.e. 1:0-1). The child table would look like so:

create table tfns (
    employee_id int not null primary key,
    tfn char(9) not null unique,
    foreign key tfns__employee (employee_id) references employees (id)
);

You could also define a view which outer joins tfns to employees in order to show all of the columns of both tables together.