I have two tables:
Employee(eid, ename, age..)
Department(deptid, dname, managerid..) //manager id references eid
How can I create a constraint on Department table such that manager's age is always > 25 ?
Add a comment
|
2 Answers
A constraint cannot contain a subquery so a trigger is needed if you want to enforce this business rule on database level. Something like this.
create or replace trigger dep_briu_trg
before insert or update on department
for each row
declare
l_age employee.age%type;
begin
select age
into l_age
from empoyee
where id=:new.managerid;
if l_age<=25 then
raise application_error(-20000,'Manager is to young');
end if;
exception
when no_data_found then
raise application_error(-20000,'Manager not found');
end;
BTW Never store age in a table. It's different every day.
1 Comment
Damien_The_Unbeliever
And, of course, this trigger won't pick up if the employee's age is subsequently corrected to be less than 25.
In Oracle 11g, you could use a virtual column to be the target of a foreign key:
CREATE TABLE emp (eid NUMBER PRIMARY KEY,
age NUMBER NOT NULL,
eligible_mgr_eid AS (CASE WHEN age > 25 THEN eid ELSE NULL END) UNIQUE
);
CREATE TABLE dept (did NUMBER PRIMARY KEY,
mgr_id NUMBER NOT NULL REFERENCES emp (eligible_mgr_eid) );
