UNIQUE NONCLUSTERED with WHERE clause in TABLE CREATE?

Question

I am aware that it is possible to create UNIQUE constraints with a WHERE clause for a field in an existing table, but is it possible to create them in the CREATE TABLE statement? ie something like (I know this is invalid syntax):

CREATE TABLE [ExampleTable]
(
    [ID]    INT IDENTITY(1,1) PRIMARY KEY   NOT NULL,
    [Name]  NVARCHAR(20)    DEFAULT ''  NOT NULL UNIQUE NONCLUSTERED WHERE [Name] <> ''
)

With the aim here being that the [Name] field must be unique, unless it's a blank string.

score 0 · Answer 1 · answered Jun 20 '14 at 18:12

No, you can't.

You can create a filtered index but not a filtered constraint, the syntax does not allow that.

[ CONSTRAINT constraint_name ] 
{ 
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR =fillfactor ] 
        [ WITH ( index_option [, ...n ] ) ]
        [ ON { partition_scheme_name (partition_column_name) 
            | filegroup | "default" } ] 
    | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name 
            [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}

UNIQUE NONCLUSTERED with WHERE clause in TABLE CREATE?

1 Answers1