Paul White's answer is excellent for SQL 2016, onwards. In prior versions 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.

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.