CODEWITHSUNDEEP
sql-serversql-server-2012ssms
niico
niico

Reputation: 12729

Make Unique int column that allows NULLS in SSMS

How can I make an int column unique - on an existing table with data in - but allow multiple nulls - in SSMS.

There are many records in the table now - and they all have NULL in this column now.

I have seen ways to do this using a unique filtered index in TSQL - and I can see ways in the UI to do it without allowing NULLS.

Is this just not possible using the SSMS GUI?

As an aside what's the best way to do this just using pure TSQL? A unique filtered index?

Upvotes: 1

Views: 84

Answers (2)

SqlZim
SqlZim

Reputation: 38023

With code:

create unique nonclustered index uixf_mycol
  on dbo.t (col)
    where col is not null;

In SSMS:

  1. create a new nonclustered index by right clicking the index folder from the expanded table in object explorer
  2. add the column
  3. check the unique checkbox
  4. add the where clause in the filter panel.

enter image description here

enter image description here

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

You can create UNIQUE constraint on the column.

CREATE TABLE Test
(
P_Id int,
CONSTRAINT uc_ID UNIQUE (P_Id)
)

Upvotes: 0

Related Questions