Q

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close