Oracle Unleashed- P11: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products. | cause all references in the child table to be deleted automatically. In this respect using ON DELETE CASCADE is similar to creating a DELETE trigger on the referenced table. This is an extremely powerful option and should be used with caution. If it is not properly understood unwanted automatic deletions could result. Although they are ideally suited to enforcing referential integrity REFERENCES constraints may not be desirable in certain situations. For example if a table has a high volume of transactions and contains several foreign keys that are simple lookups performance can be adversely affected by using the REFERENCES constraint. Each time a row is inserted or updated the referenced tables must be checked to ensure that each foreign key being inserted exists in the referenced tables. Depending on the nature of the data and the importance of performance it may be preferable to enforce the foreign keys through the application itself. Table Constraints Each of the column constraints described in the previous section can also be applied as table constraints with the exception of NOT NULL. Table constraints have the additional advantage of being able to operate on multiple columns. Refer to the CHECK constraint in Listing . This provides a sanity check on the lower bounds of date_of_birth but it would be better to check the upper bounds as well. Listing illustrates how you can accomplish this using a table constraint. Listing . This DDL script containing a CHECK table constraint is an improvement on the column constraint used in Listing . CREATE TABLE individual ID NUMBER 10 CONSTRAINT indiv_pk PRIMARY KEY last_name VARCHAR2 30 CONSTRAINT indiv_l_name NOT NULL first_name VARCHAR2 30 CONSTRAINT indiv_f_name NOT NULL notes VARCHAR2 255 date_of_birth DATE last_updt_user VARCHAR2 20 CONSTRAINT indiv_lu_id NOT NULL last_updt_date DATE CONSTRAINT indiv lu dt NOT NULL CONSTRAINT indiv_chk_bday CHECK date_of_birth BETWEEN TO_DATE 01JAN1845 DDMONYYYY .
