Problem solve Get help with specific problems with your technologies, process and projects.

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

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

Continued from part 1: I don't see how you can do this with a constraint (Why not?). You can write a trigger to do this (Why?).


A digression on constraints and triggers


Why can't you use a constraint for this problem?

Constraints are a convenient way to do some common validation tasks, but they can't do all possible validation. CHECK constraints can examine and compare different columns in the same row, but they can't do queries. (CHECK constraints can't even use certain functions, like SYSDATE and SYS_CONTEXT.) The other types of constraints (FOREIGN KEY, NOT NULL, UNIQUE) do very, very specific things.  

Constraints vs. triggers

Anything you can do with a constraint you can also do with a trigger. Consider what a constraint does: It raises an error (thereby stopping the transaction) whenever a DML command tries to violate some rule. You can write a BEFORE INSERT, UPDATE OR DELETE trigger to test for the same violation and raise an error. There are limits to what you can do in a trigger (for example, the restriction on querying a table while you UPDATE it), but they are not nearly as severe as the limits on what you can do in a constraint. Both constraints and triggers have the desirable feature that, once they are in place, they are automatic (you don't have to remember to invoke them when applicable) and inescapable (there's no way any process, not even SQL*Loader or imp, can evade them).

Use constraints rather than triggers whenever possible. Reasons include:

  • Easier coding. Partly because constraints are so much more limited than triggers, they require less code to do the same thing, and the code is simpler and therefore less prone to errors.
  • More efficient. Again, because you can do less in a constraint, you have fewer opportunities to do it wrong. Foreign keys are a good example: You can't create a foreign key constraint without having an index on the referenced values.
  • Self-documenting: More potentially useful information is automatically put into the data dictionary with constraints. With a trigger, all you know from the data dictionary is that there is a trigger and when it fires.
  • Consistency: Constraints guarantee that your data conforms to your business rules. Triggers only guarantee that your DML transactions comply with the rules: They do nothing for data that was put in before the trigger was created, or while it was temporarily relaxed. Oracle provides some tools (like the EXCEPTIONS INTO feature of the ALTER TABLE command, which identifies rows not in compliance with a rule) that help with constraints, but not triggers.

Dig Deeper on Using Oracle PL-SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.