Create a column with unique constraint where multiple nulls are allowed

/*
 Create a column with unique constraint where multiple nulls are allowed
 Requires PK in the table .
Trick is to create a computed column with primary key
 Example allows duplicate cusips even when they are null
*/
CREATE TABLE #security (
securityid int identity(1,1) primary key,cusip varchar(13) NULL,
cusipnull as (case when cusip is null then securityid else 0 end),
CONSTRAINT cusipnull_uinque UNIQUE (cusip,cusipnull))

INSERT INTO #security(cusip) VALUES ('abcd')
INSERT INTO #security(cusip) VALUES (NULL)
INSERT INTO #security(cusip) VALUES (NULL) -- no error
GO

SELECT securityid, cusip, cusipnull
FROM #security order by 1

UPDATE #security SET cusip = 'efgh'
WHERE securityid = 2
GO
SELECT securityid, cusip, cusipnull
FROM #security order by 1

UPDATE #security
SET cusip = 'efgh' WHERE securityid = 3 -- error SELECT securityid, cusip, cusipnull FROM #security
DROP TABLE #security