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
CHECK (??????????????? );
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
CHECK (??????????????? );
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>What you need is a unique function index:
Very nice!!!
Yes, go with that. It's better than my trigger suggestion.
Very nice!!!
Yes, go with that. It's better than my trigger suggestion.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
>>If two people simultaneously try to INSERT
I stand corrected again. ;)
I stand corrected again. ;)
ASKER
That Unique constraint worked awesome! Thanks!
create unique index emp_active_uk on employee (decode(status,'active',te