CODEWITHSUNDEEP
sql-serversql-server-2008
Silverlight Student
Silverlight Student

Reputation: 4118

can i have unique constrain on a NULLable fields?

I have a table with following scehma

CREATE TABLE MyTable  
(  
  ID                INTEGER DEFAULT(1,1),  
  FirstIdentifier   INTEGER NULL,  
  SecondIdentifier  INTEGER NULL,  
--.... some other fields .....  
)

Now each of FirstIdentifier & SecondIdentifier isunique but NULLable. I want to put a unique constraint on each of this column but cannot do it because its NULLable and can have two rows with NULL values that will fail that unique constraints. Any ideas of how can I address it on schema level?

Upvotes: 3

Views: 473

Answers (5)

Conrad Frix
Conrad Frix

Reputation: 52675

You can use a Filter predicate on the CREATE INDEX

From CREATE INDEX

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

WHERE <filter_predicate> Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.

The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

Here are some examples of filter predicates for the Production.BillOfMaterials table:

WHERE StartDate > '20040101' AND EndDate <= '20040630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20040404', '20040905') AND EndDate IS NOT NULL

Filtered indexes do not apply to XML indexes and full-text indexes. For UNIQUE indexes, only the selected rows must have unique index values. Filtered indexes do not allow the IGNORE_DUP_KEY option.

Upvotes: 0

TimothyAWiseman
TimothyAWiseman

Reputation: 14893

As several have suggested, using a filtered indexe is probably the way to get what you want.

But the book answer to your direct question is that a column can be nullable if it has a unique index, but it will only be able to have one row with a null value in that field. Any more than one null would violate the index.

Upvotes: 1

JNK
JNK

Reputation: 65217

Do a filtered unique index on the fields:

CREATE UNIQUE INDEX ix_IndexName ON MyTable (FirstIdentifier, SecondIdentifier)
   WHERE FirstIdentifier IS NOT NULL
   AND SecondIdentifier IS NOT NULL

It will allow NULL but still enforce uniqueness.

Upvotes: 0

csano
csano

Reputation: 13716

Your question is a little bit confusing. First, in your schema definition, you say that your columns are not allowed to hold null values, but in your description, you say that they can be null.

Anyway, assuming you've got the schema wrong, and you actually wanted the columns to allow null values, SQL Server allows you to do this by adding WHERE IS NOT NULL to the constraint.

Something along the lines of:

CREATE UNIQUE NONCLUSTERED INDEX IDX_my_index
ON MyTable (firstIdentifier)
WHERE firstIdentifier IS NOT NULL

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use a filtered index as a unique constraint.

create unique index ix_FirstIdentifier on MyTable(FirstIdentifier) 
  where FirstIdentifier is not null

Upvotes: 7

Related Questions