create table product ( product_id int not null identity(1,1), the_product varchar(50) not null, is_deleted int not null default 0 ); create unique index ux_product on product(the_product) where is_deleted = 0; insert into product(the_product) values('keyboard'); insert into product(the_product) values('mouse'); update product set is_deleted = 1 where the_product = 'mouse'; -- insert into product(the_product) values('keyboard'); -- won't be allowed /* For all intents and purposes, the old mouse is deleted from the system, should still allow inserting new mouse. Prior to filtered index capability(available only on Sql Server 2008), having a unique column on table with marked deleted deleting strategy is an impossible implementation */ insert into product(the_product) values('mouse'); -- will be allowed
"Simplicity can't be bought later, it must be earned from the start" -- DB
Wednesday, January 19, 2011
Deletion strategy for Sql Server 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment