Filtered Constraint When Defining a SQL Table?

Question

I wanted to set a unique constraint that allowed for nulls. I came up with filtered constraints. Great. Except... they're all post-table creation. As in:

• Step 1: define a table
• Step 2: add the constraint

Like this:

CREATE TABLE MyTable
(
    [Id] INT NOT NULL,
    [ColumnA] INT NULL,
    [ColumnB] INT NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY ([ID] ASC)
)

CREATE UNIQUE INDEX [MyNullableIndex] ON MyTable (ColumnA, ColumnB) WHERE ColumnA IS NOT NULL AND ColumnB IS NOT NULL

I want to do this in one fell swoop. As in: include the constraint in the table definition. Is this possible?

Something like this:

CREATE TABLE MyTable
(
    [Id] INT NOT NULL,
    [ColumnA] INT NULL,
    [ColumnB] INT NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY ([ID] ASC),
    CONSTRAINT [Unique_ColumnA_ColumnB] UNIQUE (ColumnA, ColumnB) WHERE ColumnA IS NOT NULL AND ColumnB IS NOT NULL
)

Or is there some kind of fancy check constraint I can use?

Thanks in advance.

Answer 1

e.g. from here:

CREATE TABLE MyTable
(
    [Id] INT NOT NULL,
    [ColumnA] INT NULL,
    [ColumnB] INT NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY ([ID] ASC),
    CONSTRAINT [Unique_ColumnA_ColumnB] UNIQUE (ColumnA, ColumnB)
)