Functional Magic II: Dynamically Enforcing Foreign Key Delete Constraints on the Client

By John C. Lennon

 

The introduction of enforced database level constraints in version 7 of the Oracle database raised the possibility of not having to concern ourselves with enforcing relational integrity in our application code. However, we have to consider if relying solely on these constraints is really desirable. A good user interface should normally be expected to warn the user as soon as possible if a validation is going to fail, not wait until commit time. (See Bradley D. Brown’s discussion of this topic in  “It’s No Game” Oracle Informant, May 1996).

 

Create or replace view

ALL_FK_CONSTRAINTS

AS

SELECT con.name   constraint_name                                                                  

      ,bt.owner#  parent_table_owner                                                               

      ,bt.name    parent_table                                                                      

      ,bc.name    parent_column                                                                    

      ,tab.owner# child_table_owner                                                                

      ,tab.name   child_table                                                                      

      ,col.name   child_column                                                                     

      ,decode(i2.refact,1,'YES','NO')  cascade_delete                                               

      ,tab.obj#   child_table_id                                                                   

      ,col.col#   child_column_id                                                                  

  FROM sys.con$  con                                                                                 

      ,sys.obj$  tab                                                                                

      ,sys.col$  col                                                                                 

      ,sys.ccol$ i                                                                                 

      ,sys.obj$  bt                                                                                 

      ,sys.col$  bc                                                                                  

      ,sys.cdef$ i2                                                                                

      ,sys.ccol$ pk

 WHERE con.con# = i.con#                                                                            

   AND tab.obj# = i.obj#                                                                           

   AND col.obj# = i.obj#                                                                           

   AND col.col# = i.col#                                                                           

   AND i2.con#  = i.con#                                                                           

   AND bt.obj#  = i2.robj#                                                                          

   AND bc.obj#  = i2.robj#                                                                         

   AND pk.con#  = i2.rcon#

   AND bc.col#  = pk.col#

   AND i2.type  = 4                                                                                 

 

/* AND i2.type#  = 4 for Oracle 8 */

 

Figure 2: SQL to Create ALL_FK_CONSTRAINTS

Maintaining delete constraints in our applications can be a time consuming task This is particularly true when an application is developed in phases, or new applications are added to an integrated system. New foreign keys to existing tables are continually being added and these constraints need to be enforced.  Code has to be revisited frequently to reflect the changes and additions. Even when using Designer, the affected forms must be identified and regenerated. One example in a real life application is a table which was referenced by ten foreign keys when phase 1 went into production. By the time phase 3 rolled out this number had risen to sixty-four.

 

The procedures to enforce RI also present a performance bottleneck. In the above example, between one and sixty-four tables must be queried when a record is to be deleted. The worst case is when the delete is permitted - all sixty-four queries will be executed. The first step in addressing the performance problem is to move the RI checking procedures from the client to the server, in stored procedures. The performance gain is twofold, the queries execute considerably more quickly on the server and network traffic is minimized. However, in most cases this will actually increase the maintenance effort.

 

Creating a Constraint View

The Oracle Data Dictionary obviously contains the definition of all the constraints in the database. By combining this information with a new feature in Oracle7, dynamic SQL, an almost zero maintenance option exists.

 

To implement this option a view is required which will show  the parent/child relationships for foreign key  constraints.

 

Unfortunately none of the views provided by Oracle have all the information required.

 

 

PACKAGE On_Record_Delete IS

 PROCEDURE Check_RI(Parent_Table_Owner IN     VARCHAR2

                   ,Parent_Table_Name  IN     VARCHAR2

                   ,Parent_Column      IN     VARCHAR2

                   ,Parent_Key_Value   IN     NUMBER

                   ,Constraint_Name       OUT VARCHAR2

                   ,Child_Table_Name      OUT VARCHAR2

                   ,Child_Col_Name        OUT VARCHAR2

                   ,Message_Level         OUT VARCHAR2

                   ,Message_Text          OUT VARCHAR2);

 

 FUNCTION Query_Child_Table(f_Table_Owner VARCHAR2

                           ,f_Table_Name  VARCHAR2

                           ,f_Col_Name    VARCHAR2

                           ,f_Col_Value   NUMBER)

  RETURN INTEGER;

 

 FUNCTION Table_Owner(Owner_Name VARCHAR2)

  RETURN NUMBER;

 

 FUNCTION Table_Owner(Owner_Number NUMBER)

   RETURN VARCHAR2;

 

END On_Record_Delete;

 

Figure 1: View ALL_FK_CONSTRAINTS

 

 

 

The first task then is to create a view of the SYS.xxx$ tables which presents the constraints in the required detail (see figures 1 and 2). This is a relatively simple exercise but represents the “almost” in almost zero maintenance. Should Oracle change the structure of the tables in a future release the view would have to be rebuilt.

 

This view will dynamically reflect constraints that are added, dropped or have their cascade delete rule changed.

 

The view needs to be created as with SYS as the owner. Although it is not strictly necessary for the procedure described in this article it is a good idea to create a  public synonym for this view and grant select privileges to developers, so that it can be used to augment the existing views.

 

A point to note is, that while the Oracle user names will be required, only the user id numbers for both parent and child table owners are included in the view. This is because the two additional joins required to get the user names from SYS.USER$ have a considerable negative performance impact due to a lack of appropriate indexes. This problem is overcome by only querying SYS.USER$ for the name or number when absolutely necessary.

 

The Database Package

The specification for the database package ON_RECORD_DELETE is shown in figure 3. The package body code is at the end of this article.

FIGURE 3 Package Specification

 

As constraints may be need to be enforced across integrated applications with different owners, this package needs to be owned by SYS, or by another user with the READ ALL TABLE privilege. Remote database connections are not supported in this version.

 

Delete Key

For an Oracle forms application a KEY-DELREC trigger similar to the example in Figure 4 is required. This example is based on a table EMPLOYEES, owned by user HR, with a primary key column EMPLOYEE_NUMBER. Any business rules, for example preventing deletion of an employee for six years after termination, should be enforced first.

 

When the user attempts to delete an employee record the KEY-DELREC trigger fires and passes the table owner name, the table name, the primary key column name and the current record’s primary key value to the packaged procedure ON_RECORD_DELETE.CHECK_RI. This procedure first checks the table owner name against the last used table owner. If these do not match the overloaded function TABLE_OWNER is called using the owner name as its input parameter. This function returns the owner’s user number, which will be used when querying the view.

DECLARE

 Message_Text     VARCHAR2(255);

 Message_Level    VARCHAR2(1);

 Constraint_Name  VARCHAR2(30);

 Child_Table_Name VARCHAR2(30);

 Child_Col_Name   VARCHAR2(30);

 

BEGIN

 

  On_Record_Delete.Check_RI

     ('HR'                  -- Table Owner

     ,'EMPLOYEES'           -- Table Name

     ,'EMPLOYEE_NUMBER'     -- Primary Key column

     ,:emp.employee_number  -- Primary Key Value

     .Constraint_Name       -- Returned Value

     ,Child_Table_Name      -- Returned Value

     ,Child_Col_Name        -- Returned Value

     ,Message_Level         -- Returned Value

     ,Message_Text);        -- Returned Value

 

  IF Message_Level = 'D'    -- Delete not allowed

   THEN

    message(Message_Text,ACKNOWLEDGE);

    raise FORM_TRIGGER_FAILURE;

  ELSIF Message_Level = 'C' -- Cascade delete will result

   THEN

    -- code as required here (optional)

    delete_record;

  ELSIF Message_Level = 'E' -- Error condition

   THEN

    message(Message_Text,ACKNOWLEDGE);

    raise FORM_TRIGGER_FAILURE;

  ELSE                     -- Message level is null

    delete_record;         -- O.K. to delete this record

  END IF;

END;

 

 Figure 4 KEY-DELREC Trigger on Form’s Block EMP

 

The procedure then executes a cursor loop which queries the view ALL_FK_CONSTRAINTS for constraints which match the table owner, parent table and parent column parameters and returns the child table owner number, table name and column and the cascade delete condition (YES/NO).

 

When a record is retrieved, if the cascade delete condition is YES and a previous record has found a child record, which may be cascade deleted, no action is taken and the cursor continues to loop. If this condition is not true, the child table owner number is checked against the parent table owner and the last child table owner variables. If one of these is a match the corresponding owner name is used otherwise function the TABLE_OWNER is called, this time using the owner number as its input parameter and the owner’s user name is returned.

 

Dynamic SQL

The child table owner name, table name, column name and the parent table primary key value are now passed as parameters to the function QUERY_CHILD_TABLE. Using the dbms.sql package the function dynamically builds a  cursor to query the child table for records which reference the parent table primary key value.


An example of this, where the parent primary  key value is 1001 and the child table to  query is

 HR.EMPLOYEE_DEPENDANTS would be:

 

    SELECT  1

      FROM hr.employee_dependants

     WHERE employee_number = 1001

 

This cursor is executed once only, as all that the procedure needs to know is whether or not any matching records exist, not how many or any associated values. The function returns 1 if a match was found, zero if not.

 

If the function returns zero then no further action is taken and the cursor loop continues. If the function returns 1 then

 

·       If cascade delete is YES the variable t_Message_Level is set to ‘C’ and the cursor loop continues.

·       If cascade delete is NO then the variable t_Message_Level is set to ‘D’, a default message is written to the OUT parameter Message_Text and the cursor loop is exited.

 

On termination of the cursor loop, whether by completion or a forced exit, the procedure execution is complete and control is returned to the calling trigger.

 

Message Levels

The action taken by the KEY-DELREC trigger depends on the value of the parameter Message_Level returned by the CHECK_RI procedure.

 

If Message_Level is NULL the trigger performs its default function,  using the built-in function, delete_record.

 

Message_Level = ‘C’ indicates that only child records that will be cascade deleted were found. The action taken in this case will be determined on a case by case basis. For example, the record could just be deleted or a warning could be issued to the user offering a choice of whether to continue with or cancel the deletion..

 

Message_Level = ‘D’ indicates that the record may not be deleted. Again the action taken can be determined by the developer, but in any event the delete_record built-in should not be called. A default message is returned in the parameter Message_Text.

 

Message_Level = ‘E’ indicates that an error has occurred while executing the procedure. The SQLERRM error message is returned in the Message_Text parameter, allowing the error to be handled in an application specific manner.

 

User Messages

There are two types of warning message generated by the procedure. One for when the constraint refers to another table and one for when it is a recursive reference to the same table (e.g. the employee to be deleted is a manager and still has subordinates). Of necessity these messages are generic and therefore not as user friendly as might be desired.

 

There are a number of possible ways of providing more explanatory and user friendly messages; unfortunately all introduce an element of maintenance. The easiest way might be to create a table CONSTRAINT_MESSAGES with two columns, CONSTRAINT_NAME and MESSAGE. The parameters and variables Constraint_Name, Child_Table_Name and Child_Col_Name, which are not used in the example, are provided so that this type of solution can be easily implemented outside of the package.

 

Another, and maybe better, way might be to store the messages as comments on the child column. Using this method the messages would be stored in the data dictionary and could be retrieved by the cursor in the Check_RI procedure. Also, if Designer/2000 (or a similar tool) is being used to build and document the database the messages would be contained in the repository as part of the column definition. Even if an application’s standards call for the comment being used for detailed documentation of each column the message could be appended to the comment. A key word or symbol between the true comment and the message would facilitate parsing the string to extract the message using substr(…).

 

If the view ALL_FK_CONSTRAINTS is modified as in shown in Figure 5 the message can be retrieved by adding the column DELETE_MESSAGE to the cursor Get_FK_Tables in the procedure CHECK_RI. The code within the IF/END IF construct which builds the message would then be replaced with

 

Message_Text := FK_Record.Delete_Message;

Create or replace view

ALL_FK_CONSTRAINTS AS

SELECT con.name   constraint_name