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 ) ) ...
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
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.
This was first published in December 2003