Unique Constraint on Pair of Fields (with boolean)

[zandsc1]

Looking for something that's similar to a unique constraint.

The scenario is that I have a table with part numbers and revisions in it. In order to maintain change history, the old revision of the part number stays in the table when the new revision is released. Now say I wanted to create a field called 'isActive' which is a boolean field. The purpose is obviously that when a new revision is released, that version becomes active and the old version is deactivated. I realize this may not be the best way to do this, but for argument's sake, I'm curious how it would be done.

Is there a way to make a unique constraint with the part number and the 'isActive' field for when 'isActive' is true? I don't care about multiple instances of the part number where 'isActive' is false.

 

[philhege]

I'm not sure about specific values, i.e. ignoring multiples of partnumber/false. You may want to consider a trigger.

-----------
With business clients like mine, you'd be better off herding cats.

 

[Thargy]

zand,

on the assumption that version numbers always increase, you simply don't need an isactive field. Just choose the max of the version number.
Does the table store two version numbers, i.e. the old and the new. If so, it's even easier, just choose the second value.

Regards

T

 

[denimined]

I am sorry to say that our code does this ***EVERYWHERE***... but it does work.

select tb.ident, 
       tb.revision, 
       <columns>
from table as tb
where <conditions>
  and tb.revision = (
                     select top 1 revision
                     from table
                     where ident = tb.ident
                     order by revision desc
                    )

Note that we have found that the above sub-query is much faster than:

and tb.revision = (
                   select max(revision) as revision
                   from table
                   where ident = tb.ident
                  )

In fact, sometimes the second format simply fails.

 

[zandsc1]

philhege - thanks for the suggestion. I think you may be right. Off to do some research on triggers.

Thargy - this would hold true given your assumption, but I'm afraid the assumption doesn't work in our case. There are instances where a new revision of a part is created, but not released yet (i.e. I create rev 6, but rev 5 is still active because I haven't processed a change order to release rev 6).

Denimined - Thanks for the SQL snippets, might prove useful if I end up going that route.