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
,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
,sys.com$
com 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 com.obj# =
tab.obj# AND com.col# =
col.col# AND i2.type = 4 /* AND i2.type# = 4 for Oracle 8 */ |
Figure
5 - Modified View ALL_FK_Constraints
Alerts
In an Oracle Forms appication alerts provide a superior user interface to the built-in message function. A simple alert, such as that shown in Figure 6 can be used in place of the message built-in and requires no programming beyond setting the text to be displayed.
|
|
Figure 6 - A Simple Alert
More complex alerts can be used to control conditional execution of program code, depending on the user’s choice. The alert shown in Figure 7 offers the user two choices, to continue with a cascade delete operation or to cancel it.
|
|
The program unit used to control the alert may be stored in a PL/SQL library and can then be accessed by multiple forms.
Figure 7 An Alert with two
buttons
|
Function
Cancel_Cascade(Message_Text Varchar2) Return
BOOLEAN IS Alert_Button
NUMBER; Alert_Id
ALERT; BEGIN Alert_id :=
Find_Alert(‘Cascade_Delete’);
Set_Alert_Property(Alert_Id
,Alert_Message_Text
,Message_Text); Alert_Button :=
Show_Alert(Alert_id); IF Alert_Button :=
ALERT_BUTTON1 THEN RETURN
FALSE; ELSE RETURN
TRUE; END IF; END
Cancel_Cascade; |
Figure 8 - Cascade Delete Alert Program Unit
The code segment in KEY-DELREC can now be re-written as:
ELSIF
Message_Level = 'C' -- Cascade delete
will result
THEN
IF NOT
Cancel_Cascade(Message_Text)
THEN
delete_record;
END
IF;
ELSIF
Message_Level = 'E' -- Error
condition
Foreign Key Indices
The procedure set out in this article has been in use in a production application for over a year and has proved extremely efficient. With more than 1400 total foreign key constraints the worst case scenario mentioned earlier executes in slightly over one second. In cases where there are only a few constraints to check the response is almost instantaneous. To achieve this level of performance however it is essential that all the foreign key columns are indexed.
About the Author
John Lennon is a Project Manager with Dulcian, Inc.
He has been working with Oracle products in South Africa and the United States since 1988.