1

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
)
Asghwor
  • 353
  • 5
  • 16

1 Answers1

2

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Interesting, I did check the `CREATE TABLE` grammar. But `` doesn't mention a `WHERE` clause. Must be out of date! https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16 – Martin Smith May 30 '22 at 16:01
  • I can only assume that it's implicit included in the `| [ ] }` section, @MartinSmith . But I agree, it's not in the ` ::=` section. I might send a revision if I get the chance. – Thom A May 30 '22 at 16:02
  • 2
    [Pull request](https://github.com/MicrosoftDocs/sql-docs/pull/7628) sent, @MartinSmith . – Thom A May 30 '22 at 16:08