CODEWITHSUNDEEP
sql-serversql-server-2008
Yakalent
Yakalent

Reputation: 1152

combination unique with one column null

I got a table example:

create table the_entity
(
 id integer,
 number integer not null,
 chair  integer null,
 primary key(id)
);

i want that the combination of the columns number and chair is unique. Column chair is nullable. Is this possible without using filtered index ?

Upvotes: 0

Views: 71

Answers (1)

SqlZim
SqlZim

Reputation: 38023

A filtered index is the only way to do that and allow for multiple nulls for the same number. A unique constraint/unique index will allow one null per number.

The answers to a similar question on dba.stackexchange explain more: https://dba.stackexchange.com/questions/80514/why-does-a-unique-constraint-allow-only-one-null

Upvotes: 1

Related Questions