Design optional unique business keys in SQL Server

An account may exist before an external system assigns its identifier. Many accounts can therefore have no external ID, but two accounts in the same tenant must not share a known one. This is a precise business rule, and a conventional nullable UNIQUE constraint does not automatically express it.

Define which rows participate in uniqueness

For a single nullable key column, SQL Server's ordinary unique index permits only one NULL entry. With a composite key, uniqueness applies to the entire combination. Neither behavior means 'ignore every row whose optional field is absent'. A filtered unique index lets you state that participation rule explicitly.

The example enforces uniqueness of ExternalId within TenantId only for rows with a non-NULL external identifier.

CREATE TABLE #Accounts
(
    AccountId int NOT NULL PRIMARY KEY,
    TenantId int NOT NULL,
    ExternalId nvarchar(100) NULL
);
CREATE UNIQUE INDEX UX_Accounts_External
ON #Accounts (TenantId, ExternalId)
WHERE ExternalId IS NOT NULL;

INSERT #Accounts VALUES
(1, 10, NULL), (2, 10, NULL),
(3, 10, N'ABC'), (4, 20, N'ABC');

BEGIN TRY
    INSERT #Accounts VALUES (5, 10, N'ABC');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS DuplicateError;
END CATCH;

SELECT AccountId, TenantId, ExternalId
FROM #Accounts ORDER BY AccountId;
DROP TABLE #Accounts;

Both missing identifiers in tenant 10 are accepted. ABC can appear in tenants 10 and 20 because the tenant is part of the key. The attempted second ABC in tenant 10 fails, and the final result still contains four rows. The filter determines which rows participate; the index key determines which participating rows collide.

Keep a separate stable primary key such as AccountId for relationships. An external identifier may arrive late, change, or be retired. Requiring child tables to follow that lifecycle can complicate an otherwise stable internal model. A filtered unique index also does not provide a general foreign-key target for rows outside its filter.

Make the meaning of equal explicit

NULL, an empty string, and a string of spaces are not interchangeable input states unless your application makes them so. If blank input means missing, normalize it deliberately before storage and enforce the accepted representation. Otherwise an empty string participates in the filtered index as a real value and can collide.

String equality follows the column's collation and SQL Server comparison rules. Case sensitivity and accent sensitivity can change whether two external identifiers are treated as equal. Trailing spaces can also compare equal for ordinary character comparisons. Choose the contract based on the external system, not on whichever database default happened to exist.

If a supplier distinguishes identifiers that your current collation considers equal, do not silently collapse them with a lowercase or trimming function. Conversely, if the business considers multiple representations equivalent, normalization should be consistent across imports, APIs, and maintenance scripts. A normalized stored column can make that rule visible, with constraints or controlled writes ensuring it stays synchronized.

Before creating the unique index on existing data, group candidate non-NULL values by the exact intended key and inspect duplicates. Perform the audit using the intended comparison semantics. Decide which account owns the identifier and how dependent records should be handled; deleting arbitrary duplicates is a business decision, not index maintenance.

Let the database arbitrate simultaneous assignments

An application SELECT that finds no matching ID is useful for a friendly message but cannot guarantee uniqueness. Two requests can both pass that check before either inserts. The unique index is the final arbiter. Catch the duplicate-key error and translate the relevant violation into an understandable conflict response.

An update from NULL to a known value must follow the same rule as an insert. So must changing TenantId on an existing account. Test both transitions, because a form may expose them through different code paths. Also test two sessions assigning the same value concurrently, not just sequential duplicate inserts.

If soft-deleted accounts should release their identifier, add that requirement to the participating-row rule and decide what restoring an old account means. A restore can legitimately conflict with a newer owner. If identifiers must never be reused, retaining uniqueness across archived records may be the correct policy instead.

Finally, review the index as both an integrity rule and a maintained structure. Its creation can fail on existing duplicates and requires deployment planning on a large table. Keep the required session SET options consistent for filtered indexes. Document the business rule in plain language next to the schema migration so future changes preserve the intended boundary.

Technical references: Microsoft Learn: Unique indexes · Microsoft Learn: Filtered indexes.