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.

It may serve the purpose though, "UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. " OTOH, if you want multiple nulls, create a filtered index on the table using the same criteria — user1443098, Apr 20 '18 at 15:57
It will work, @Cool_Br33ze, but I'm looking to see if I'm just missing something is all. I'm looking for the BEST solution, not just "I made it work." — vbullinger, Apr 20 '18 at 16:35

user1443098 · Answer 1 · 2018-04-20T18:07:58.067

-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)
)

edited Apr 20 '18 at 18:07

answered Apr 20 '18 at 18:01

user1443098

6,487
5
38
67

Thanks, but this is exactly what I referenced at the beginning of my question. This is a filtered index added AFTER creation of the table, not during table creation. – vbullinger Apr 20 '18 at 18:03
That doesn't allow for column a and column b to be null. Please re-read my question. I want to allow them to be null – vbullinger Apr 20 '18 at 18:11
no, it does allow them to be null. It's just that there can be only one occurrence of that, as I mentioned before. That's what I wasn't sure about wrt your requirements. Otherwise, your function will do the trick. It still is worth adding an NCI on those two columns though, since the lookup will happen with every INSERT/UPDATE request. – user1443098 Apr 20 '18 at 18:34
It just... seemed awfully clear that I said I wanted to allow nulls. Not one null value. I want to ignore the nulls so you can have more than one. Guess it wasn't very clear – vbullinger Apr 20 '18 at 18:53

Filtered Constraint When Defining a SQL Table?

1 Answers1