Ask the Expert

A one to not-too-many relationship, part 1

I am trying to add the following constraint in CREATE TABLE to restrict the number of students a tutor can supervise, and I am getting the error 'ORA-02251: subquery not allowed here'. Can you help?

 CREATE TABLE Undergraduate ... , Tutor CHAR(6) NOT NULL CONSTRAINT TooManyUG CHECK ( NOT EXISTS ( SELECT Tutor FROM Undergraduate GROUP BY Tutor HAVING COUNT (*) > 30 ) ) ...


I

    Requires Free Membership to View

don't see how you can do this with a CONSTRAINT (Why not?). You can write a trigger to do this (Why?), but it involves a little trickery. If you write a BEFORE INSERT OR UPDATE trigger on undergraduate, and in that trigger you query undergraduate to see how many students are already assigned to the tutor, then you get an "ORA-04091: table undergraduate is mutating" error. In Oracle9, you can write INSTEAD OF triggers on a view, so if you have this view:

 CREATE OR REPLACE VIEW vUndergraduate AS SELECT * FROM Undergraduate;

Then you can put this trigger on the view:

 CREATE OR REPLACE TRIGGER vundergraduate_iiu INSTEAD OF INSERT OR UPDATE ON vundergraduate FOR EACH ROW DECLARE max_tutee_cnt PLS_INTEGER := 2; -- max undergraduates/tutor too_many_tutee_exn EXCEPTION; tutee_cnt PLS_INTEGER; BEGIN SELECT COUNT (*) INTO tutee_cnt FROM undergraduate WHERE tutor = :NEW.tutor AND id NOT IN ( :NEW.id, NVL (:OLD.id, :NEW.id) ); -- The NVL is just so that the SELECT INTO can be used when -- inserting, and :OLD is undefined. IF tutee_cnt >= max_tutee_cnt THEN RAISE too_many_tutee_exn; ELSE IF INSERTING THEN INSERT INTO undergraduate ( id, tutor, ...) VALUES (:NEW.id, :NEW.tutor, ...); ELSIF UPDATING THEN UPDATE undergraduate SET id = :NEW.id , tutor = :NEW.tutor , ... WHERE id = :OLD.id; END IF; END IF; EXCEPTION WHEN too_many_tutee_exn THEN RAISE_APPLICATION_ERROR ( -20001, 'Maximum number of undergraduates per tutor (' || TO_CHAR (max_tutee_cnt) || ') exceeded' ); END; / SHOW ERRORS

(Note: line 1 of the trigger is the DECLARE statement: remember this if you do get a compilation error referring to a specific line.)

Use the view, not the base table, for all DML.

If you're using a version of Oracle earlier than 9.0, you can't have an INSTEAD OF trigger. You can still do what you want, but the solution is even less convenient than in version 9. You can create another table to replicate the parent-child relationship, then query that table before doing DML on undergraduate (and do the corresponding DML on it after changing undergraduate). Another solution is to keep a counter of tutees in the tutor table (again, a trigger on undergraduate will query it before allowing any DML, and update it when successful).

Click for part 2.

This was first published in December 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: