How would I write a Constraint to ensure that there is only one record that is active for any one templ_id.

Hi, How would I write a Constraint to ensure that there is only one record that is active for any one templ_id.

This is valid... but two actives ones would not be valid.
templ_id       status
 1                 inactive
 1                 active
 1                 inactive



ALTER TABLE employee
    ADD CONSTRAINT unq_templ_id_active_status_ck
    CHECK (??????????????? );
LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
slightwv (䄆 Netminder) Commented:
I don't believe to can do this with a simple constraint.  You definitely can with a trigger.

0
 
AkenathonCommented:
Check constraint do NOT span more than one record. What you need is a unique function index:

create unique index emp_active_uk on employee (decode(status,'active',templ_id,null),decode(status,'active','active',null))
0
 
slightwv (䄆 Netminder) Commented:
>>What you need is a unique function index:

Very nice!!!

Yes, go with that.  It's better than my trigger suggestion.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AkenathonCommented:
Thanks! A couple of notes for the author:

- A unique CONSTRAINT is no good here, since the syntax only takes columns (i.e. no functions allowed)
- The suggestion works because NULL keys are not indexed
- You can leave out the second field I'm indexing, since the point is indexing only the "active IDs" anyway:

create unique index emp_active_uk on employee (decode(status,'active',templ_id,null))
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
AkenathonCommented:
Forgot to mention: the problem with triggers is concurrency. If two people simultaneously try to INSERT the same ID with 'active' status, the only way you can enforce uniqueness is having an autonomous transaction insert the ID into an auxiliary structure and fail if there's a duplicate. In effect, you'd be implementing the unique index anyway...
0
 
slightwv (䄆 Netminder) Commented:
>>If two people simultaneously try to INSERT

I stand corrected again.  ;)
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
That Unique constraint worked awesome! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.