A script-enabled browser is required for this page to function properly.

About Declarative Database Constraints

A database constraint is a rule that governs the logical integrity of your data. The rule is declared at table creation time, or can be added (with some restrictions) after the fact to an existing table.

Entity constraints

Entity constraints can declaratively enforce a column (or ordered group of columns) to be NOT NULL, UNIQUE within the table, have a DEFAULT value if none is specified on INSERT, and/or satisfy a more complex logical condition given in a CHECK clause. These constraints provide intra-table integrity.

Referential constraints

Referential constraints provide the logical link between a master and a detail table by establishing primary and foreign key relationships. Database constraints protect your data by automatically enforcing at the database level the rules you have declared. If you create a record in a master table and one detail record, for example, the default operation of database constraints restricts updating of the primary key of the master record. Default database constraints also prevent deleting of the master record (unless the ON DELETE CASCADE option is used when declaring all of the foreign key references).

Strategies for Constraint Checking

Prior to Oracle7 Server, nearly all data integrity was provided at the application level--for form bulider, through triggers. The goal of application-side logic is to stop bad data before it happens.

The Oracle Database ability to perform implicit data verification at the kernel level means that current systems that had been coded to guarantee data integrity by the application will in essence be checking the same things twice. To optimize, you might be tempted to strip out all application logic and leave all constraint checking to the database. However, you should weigh the benefits that constraints on both sides provide and determine if checking things twice is too costly.

Database constraints provide the ability to centrally protect the integrity of the data without necessarily coding the logic into each one of the tools. However, the reason that logic was coded into the application in the first place was to give the user immediate feedback on errors, facilitating their rapid correction with online help and appropriate error messages.

No user would appreciate entering a batch of fifty new orders, only to attempt to execute a commit and learn from ORACLE that the fourth order violated an integrity constraint.

You will most likely prefer to check the data both at data entry time and at commit time, recognizing that the small overhead of checking is well worth the additional security it provides. Database Constraints checking is more efficient than its application-based counterpart since constraints are processed intelligently by the kernel, completely within the realm (and RAM) of the kernel, and without additional network trips back to the client.

Note that if a column has been declared to have a DEFAULT value, the kernel will assign the DEFAULT value only when the record is INSERTed, provided that the given column is not Listed among the columns in the INSERT clause. Because Oracle Forms always Lists ALL of the columns (i.e., database fields) in a block for INSERTS and UPDATES, the DEFAULT will never automatically be assigned unless the user either removes the field in question from the block (or marks it as non-database), or else codes an ON-INSERT trigger to override the normal Oracle Forms insert processing.

Also, be aware that some forms-based operations may become unnecessary when constraints are enabled. For example, deleting detail records when a corresponding master is removed, is internally handled by the kernel if the ON DELETE CASCADE option is specified for the parent-child foreign key relationship.

Master/Detail Blocks and Referential Integrity

Applications that include master-detail blocks coordinated via the automatically generated triggers and procedures in SQL*Forms 3.0 should require no modification when running against tables with Declarative Referential Integrity constraints enabled. Oracle Forms default logic will prevent the deletion of a master record when outstanding detail records exist, unless the design specified the Cascading Deletes option when creating the detail block. In that case, Oracle Forms uses a PRE-DELETE trigger to first delete the detail records, then deletes the master.

The order of operations was not critical running under ORACLE Version 6, but an application that had moved the standard PRE-DELETE logic to the POST-DELETE trigger instead would encounter problems running against Oracle Database Server when Primary/Foreign-Key relationships have been declared and enabled on the server-side.

Waiting until the POST-DELETE trigger to delete the detail records will cause Oracle Forms to issue the DELETE statement for the master record while detail records remain, and an error will be generated by the kernel:

ORA-02292: violated integrity constraint (OWNER.CONSTRAINT)
  -- detail record found

However, if the foreign key relationship is specified with the ON DELETE CASCADE option, then no problem will arise, and the Oracle Forms POST-DELETE trigger will be needlessly performing an extra DELETE statement to remove the detail records that the Cascade Delete of the master already deleted.

So potentially any INSERT, UPDATE, or DELETE in your applications could generate an error caused by violating an enabled constraint.


About database triggers

Creating a database trigger