SQL Conditional Unique Constraint With Where Clause Within Same Table -


i have table want ensure combination of 5 columns remain unique within table. example:

alter table [dbo].[mytable]      add constraint [uq__mytable.mfg.model.class.depiction.iteration]          unique nonclustered          (          [manufacturerid] asc,          [model] asc,          [blockclassid] asc,          [blockdepictionid] asc,          [blockiterationid] asc         )with (pad_index  = off, statistics_norecompute  = off,              sort_in_tempdb = off, ignore_dup_key = off,              online = off, allow_row_locks  = on, allow_page_locks  = on)      on [primary] go 

i want exclude combinations sixth separate column has particular value. example, want enforce above constraint when column [flag] = 0 , exclude enforcement when column [flag] = 1 .

as workaround, can proper ansi behavior in sql server 2008 , above creating unique, filtered index.

create unique nonclustered index [ix__mytable.mfg.model.class.depiction.iteration]  on [dbo].[mytable] ([manufacturerid],[model],[blockclassid],[blockdepictionid],[blockiterationid]) [flag] = 0; 

technet article


Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -