Removing
Obsolete Data
One of my
clients recently discussed their irritation at deleting obsolete records in
parent tables that had many child table relationships. They had developed an
extensive cleanup process to back-into their table relationships, deleting
records from the child tables first then moving upwards in through the table
relationships by following their database design charts. While I certainly
understand that and similar methods, I think we can improve upon the process by
designing databases more efficiently and changing some table designs. One thing
I am going to cover here is the result of designing and altering tables to deter
the initial problem. We know that due to referential integrity the parent-child
relationship will be maintained by the database. So let us change the rules a
little. We are going to discuss how to use ON DELETE CASCADE upon our FOREIGN
KEYS and alleviate the problem of cleaning up our database of obsolete data
connected with parent-child relationships.
Foreign
Keys
The idea behind creating
foreign keys is to develop relationships that connect tables together. Therefore,
the first table (parent) has a column that references a column in another table
(child). The column in the first table can also reference another column within
itself (self-reference). The most common foreign key relationship we usually
think of is the former one (parent-child).
Figure 1
Parent-Child relationship
Between
store.employees2.DIVISION_ID and store.divisions.DIVISION_ID
In Figure 1, we can see that the employee table has a Foreign Key column named
EMPLOYEES2_FK_DIVISIONS connecting it to the division table Primary Key DIVISION_ID column. In this way, these two tables are linked and
related.
Figure 3 DIVISION table
We also see that listed in Figure 2, that the EMPLOYEE table has a foreign key named “EMPLOYEES_FK_DIVISIONS” in the R_CONSTRAINT_NAME column and the associated constraint named “DIVISIONS_PK” which obviously matches the DIVISIONS_PK constraint in the Figure 3 where the DIVISIONS table has it listed as the DIVISIONS table’s Primary_Key. Figures 4 & 5 illustrate the CREATE TABLE syntax under Oracle 11g used to create these tables.
Figure 4 SQL to
create the table EMPLOYEES2
Figure 5 SQL to
create the table DIVISIONS
Deleting
the Child Relationships Through the Parent Connection
A useful purpose that can be
extended through the use of foreign keys is in the maintenance and cleanup of
old data, the very point of this article. By using the ON DELETE CASCADE clause
you can specify that through the FOREIGN KEY, that is, a Parent table which is
having a row deleted, can act as the catalyst to allow any matching rows to be
deleted in associated Child tables. After identifying these table relationships
in your database design, you could write the following ALTER TABLE syntax (Figure
6) while you are in the creation mode to prepare for future maintenance ahead
of time.
Figure 6 ON DELETE
CASCADE
By
logically choosing the correct data flow, you can prepare your design to remove
all associated records, that is child records, when a parent record is deleted.
Consider a company that closes Division X after moving some employees to
Division Y. When Division X is finally removed from the database, its Primary
Key will drop and all associated Foreign Key relationships should drop as well.
This is because the employee records still remaining and associated with
Division X, drop as a result of the ON DELETE CASCADE.
Play with
this concept yourself in some test records that simulate your actual experience
to get the concept clear in your mind. Then develop your new database with this
concept in mind. Moreover, look at your current database and plan some table
upgrades into your next iteration of software upgrades where this method of
maintenance will streamline your database maintenance operations going forward.
Have an Insightful day!
Robert Majdak, Co-Founder
Crystal
Majdak, Co-Founder
Management Insights
No comments:
Post a Comment