[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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 (??????????????? );
0
BILL Carlisle
Asked:
BILL Carlisle
  • 3
  • 3
2 Solutions
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now