MSSQL: Add Unique Constraint at Create Table and allow NULLs

Question

Sorry, probably noob question, but I couldn't find an answer.

CREATE TABLE SomeTable
(
   Id DECIMAL NOT NULL,
   UserIdentifier NVARCHAR(100) NULL,
   PRIMARY KEY (Id),
   ????
)

How to add a unique key constraint within the CREATE TABLE on column UserIdentifier which allows Nulls?

I know outside CREATE TABLE you could say ... WHERE UserIdentifier IS NOT NULL, but within?

Thanks!

Ah, I only now see you're asking for the inline syntax for a filtered unique index, embedding the index within the `CREATE TABLE`. That's easy: there isn't one. Unfortunately, T-SQL is not fully consistent in this manner. This typically doesn't matter much unless you absolutely, positively have to have a single statement for some reason. Note also that unique *constraints* (not indexes) do not allow filtering; this is only possible for unique indexes. — Jeroen Mostert, Aug 23 '19 at 08:08
maybe [this](https://stackoverflow.com/questions/31947263/duplicate-null-value-violation-on-unique-key-constraint-in-mssql/31949874#31949874) can help you. You can create unique index that allows null for multiple records — GuidoG, Aug 23 '19 at 08:31

Thom A · Accepted Answer · 2022-08-03T19:47:26.287

Assuming that you want multiple rows with the value NULL, you won't be able to use a UNIQUE CONSTRAINT, as NULL is still a value (even if an unknown one). For example:

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL,
                            CONSTRAINT UC_UI UNIQUE (UserIdentifier));
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL);
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL);
GO
DROP TABLE dbo.YourTable;

Notice the second INSERT fails.

You can, instead, however, use a conditional UNIQUE INDEX:

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL);

CREATE UNIQUE NONCLUSTERED INDEX UI_UI ON dbo.YourTable(UserIdentifier) WHERE UserIdentifier IS NOT NULL;

GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL); -- Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(NULL); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Steve'); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Jayne'); --Success
GO
INSERT INTO dbo.YourTable (UserIdentifier)
VALUES(N'Steve'); --Fails
GO
DROP TABLE dbo.YourTable;

As Jeroen Mostert stated in the comments though, you cannot create a unique index as part of creating the table; it must be created in a separate statement. There is no syntax to create an UNIQUE INDEX as part of a CREATE TABLE statement.

You can create this inline (it was undocumented at the time of this answer was originally written) with either of the following syntax in SQL Server 2016+:

CREATE TABLE dbo.YourTable (UserIdentifier nvarchar(100) NULL INDEX UI_UI UNIQUE WHERE UserIdentifier IS NOT NULL);

CREATE TABLE dbo.SomeTable (UserIdentifier nvarchar(100) NULL,
                            INDEX UI_UI UNIQUE (UserIdentifier)  WHERE UserIdentifier IS NOT NULL);

db<>fiddle 2014, db<>fiddle 2016

FYI, @Powerslave, this has been updated, as you *can* do this inline (at the time I'm not sure it was documented) and this is newer syntax; looks like it was added in SQL Server 2016. — Thom A, Aug 03 '22 at 15:40

score 1 · Answer 2 · answered Aug 23 '19 at 08:04

1

It is quite simple. If you want to add on single column then apply on column level by just adding UNIQUE keyword.
But if you want this to be on basis of multiple column then need to add CONSTRAINT. For more info you may find this link .link

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL UNIQUE,   --- FOR ADDING ON ONE COLUMN
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)   --- FOR MULTIPLE COLUMNS
);

answered Aug 23 '19 at 08:04

DarkRob

3,843
1
10
27

Yes, but I think the OP's question was about applying a `UNIQUE` constraint on a column that allowed NULLs (in the actual Table Creation definition). There appears to be an option for adding it afterwards: `CREATE UNIQUE NONCLUSTERED INDEX [UniqueExceptNulls] ON [SomeTable] (UserIdentifier) WHERE [UserIdentifier] IS NOT NULL` – Jonno Jun 25 '22 at 16:50

score 0 · Answer 3 · answered Aug 23 '19 at 08:04

0

In SQL Server using UNIQUE keyword.

CREATE TABLE SomeTable
(
   Id DECIMAL NOT NULL,
   UserIdentifier NVARCHAR(100) NULL UNIQUE,
   PRIMARY KEY (Id)
)

and when trying to insert same value again in the column, it will throw error.

Please find the same execution in the demo on db<>fiddle

answered Aug 23 '19 at 08:04

Arulkumar

12,966
14
47
68

2

That won't allow `NULL`s though. Well, it will allow a single `NULL`, after which it will throw a constraint violation error. – Diado Aug 23 '19 at 08:17

MSSQL: Add Unique Constraint at Create Table and allow NULLs

3 Answers3