Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-10
7
Medium Priority
?
367 Views
Last Modified: 2012-05-11
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 (??????????????? );
0
Comment
Question by:bcarlis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 35098554
I don't believe to can do this with a simple constraint.  You definitely can with a trigger.

0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35098592
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35098648
>>What you need is a unique function index:

Very nice!!!

Yes, go with that.  It's better than my trigger suggestion.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 11

Accepted Solution

by:
Akenathon earned 400 total points
ID: 35098920
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
 
LVL 11

Expert Comment

by:Akenathon
ID: 35098956
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35098983
>>If two people simultaneously try to INSERT

I stand corrected again.  ;)
0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 35101675
That Unique constraint worked awesome! Thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question