SQL SERVER 2008 How to enforce Integrity Scenario

[FishGuy]

I have set up the unique index below

CREATE UNIQUE NONCLUSTERED INDEX [IX_Products__supplier_id__product_code__active__default] ON [Live].[tbl_Products]
(
[supplier_id] ASC,
[Product_code] ASC,
[active] ASC,
[product_code_default] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This allows me to stop multiple product codes from the same supplier being entered as the default and active.

However I really only want to enforce this on active = true, I dont really care if there are multiples in there which are marked as active = false. I have come across this problem when trying to update a product to being in active and the index wont let me because there is already an identical inactive record.

[techgnome]

Thing is, the index needs to be either Unique or not.... it can't be sometimes unique...
The only other way I could see it working would be to also include some kind of IDENTITY field (or some kind of counter type field) in the index, so that when you turn one from Active = True, to Active = False, then the row is still unique... seems like a hack, but it might work... unfortunately it would also mean that the field is included for ....


Ah... here you go... (and I can't believe I'm about to suggest this).
Here's how I think I might do it..
Add a counter field, non null, defaults to 0. for simplicity I'm going to call it "sequence"
expand the unique index to include the sequence field.
Then write a trigger (gads, I can't believe I'm suggesting this) that when checks to see what field is being updated, if it's the Active field and is being set to false, and if so, gets the MAX sequence for the supplier_id and Product_code in the table, adds 1 to it, then updates the row setting sequence to this new number - that will allow the inactive items to maintain their uniqueness. If the active field is being updated and set to true, set the sequence number to 0... if that should then trigger a unique index violation if one exists.

It really is a hack... and you might be able to do it w/o a trigger as well, but you then need to be uber careful with your updates, and that also assumes that people that come along afterward also play by the rules. Using the trigger will "black box" it to some extent.

-tg