How to define a nullable and unique column inside CREATE TABLE in SQL Server?-CodePudding

I had a table defined as follows:

CREATE TABLE MY_TABLE (
  -- ...
  MY_COLUMN VARCHAR(100) UNIQUE NOT NULL
)

This definition, however, caused problems when having MY_COLUMN as NULL in multiple rows, so I changed it to:

CREATE TABLE MY_TABLE (
  -- ...
  MY_COLUMN VARCHAR(100) 
)

CREATE UNIQUE INDEX uq_my_column_not_null
ON dbo.MY_TABLE(MY_COLUMN)
WHERE MY_COLUMN IS NOT NULL;

This solved that problem, but I needed to create a constraint outside the CREATE TABLE expression, since it didn't allow me to put the WHERE inside it.

Is there a way to get this functionality without writing code outside the CREATE TABLE expression? Something like:

CREATE TABLE MY_TABLE (
  -- ...
  MY_COLUMN VARCHAR(100) UNIQUE ALLOW NULLS
)

CodePudding user response：

Yes, you can create an index (including filtered UNIQUE indexes) within the CREATE table statement, but you can't define it along side the column, you have to define the INDEX specifically:

CREATE TABLE dbo.MyTable (MyColumn varchar(100) NULL,
                          INDEX UQ_MyColumn UNIQUE (MyColumn) WHERE MyColumn IS NOT NULL);

db<>fiddle