CODEWITHSUNDEEP
sql-servert-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

SQL Server 2012 Create unique index with constraint on table creation

I'm using database projects in Visual Studio and I'm looking to make a unique index with a where clause on table creation, rather than having to create the table and add another script to add the constraint to the index. My constraint is

CREATE UNIQUE NONCLUSTERED INDEX ix_IdNotNull
ON MyTable(MyId)
WHERE MyId IS NOT NULL;

I'm looking for something like

create table MyTable
(
    MyId int unique where MyId is not null
)

but SSMS doesn't like this. Is it possible to assign a where clause to a unique constraint when the table is created?

Upvotes: 0

Views: 284

Answers (1)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

Add NC UNIQUE index

For sql 2016 +

create table MyTable
(
    MyId int ,

    INDEX [i_MyTable] UNIQUE NONCLUSTERED (MyId) WHERE [MyId] IS NOT NULL
)

early version

CREATE UNIQUE NONCLUSTERED INDEX [i_MyTable]
ON [MyTable] (MyId)
WHERE [MyId] IS NOT NULL

Upvotes: 1

Related Questions