SQL - Foreign Keys

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.

Video Presentation

Have an Insightful day!

Robert Majdak, Co-Founder

Crystal Majdak, Co-Founder

Management Insights

SQL - Savepoints

Overview

Savepoints are a very handy feature in Oracle especially during development or data manipulation. The basic idea is that as a set of transactions are being accomplished; you can create a point in time to return the data without having to completely redo all work done. That is a tremendously valuable feature, let’s say you moved through 6 of 10 steps into your development process and decided you needed to only back track 1 step. With an intermittent savepoint in place, you can go backwards to that point into your development instead of rolling back all of your work in that session saving you valuable redevelopment time.

Here is How it Works

            I am going to setup some holiday pay bonuses now while I have plenty of time to consider how they hit our budget. For this discussion, I am going to limit the increases to department #80. The bonus is a function of a percentage of the employee’s salary and commission with the commission being used to decide the bonus multiple. The script used to populate the initial bonus data in the rec_merit_incr field of the employee table is shown in Figure 1.

Figure 1. Initial bonus data calculations.

            The commission breakpoint is .25 which decides whether an employee receives the .1 or the .075 bonus calculation. I would like to consider bumping the .075 up to .095 to get an updated sum for the holiday budget. Up to this point I have created new columns and populated data in support of the project. For the work so far set in place, I will create a SAVEPOINT named merit1 as shown in Figure 2. I name it in a way that another colleague will not likely create a similar SAVEPOINT name during this development session. Then I run the SAVEPOINT command.

Figure 2. Created SAVEPOINT.

      I will now continue the work by updating the calculation to .095 and running the SQL script for that subset of employees under consideration. You can easily see how employees like Christopher have a new merit increase test value. In aggregate, I decide that the increase for the entire employee subset is not yet budget supported so I return the data back to the original values in the system when SAVEPOINT merit1 was created.

Figure 3. Updating script and subsequent output.

Rolling back the data to the SAVEPOINT

            All I need to do to return the data to the pre-merit1 SAVEPOINT values is to enter the command into the system to rollback the data up to the merit1 SAVEPOINT. The system will then respond with "Rollback complete". See Figure 4.

Figure 4. Rolling back the data to the specified SAVEPOINT.

            We can then verify the data is in the correct state by verifying it using the script I used earlier before the .075 was changed to .095.

Figure 5. Data after the "ROLLBACK TO SAVEPOINT merit1" command.

Keep in mind that another scenario we could play out is to reduce other employees with higher bonus values or blend the subset with a budget compromising overall average. We might also consider budget-rebalancing ideas. Whatever the approach to be taken, we can use the SAVEPOINT feature in any transaction analysis to lockstep our way backwards as redevelopment dictates.

Video Highlights

Have an insightful day!
Robert Majdak, Co-Founder
Crystal Majdak, Co-Founder
Management Insights Team

SQL – Pivot and Unpivot

Pivot

                                              

This week’s topic is aimed at understanding how database developers can deliver pivot and unpivot data orientation to data output. The Pivot and UnPivot clauses are new to Oracle Database 11g, the database that I am using in this article. The development environment I am using in the making of the screen captures is Oracle Developer Studio IDE. Often times Excel users need to take database output through reports they receive and massage it themselves in excel by creating pivot tables in order to view the data in a way that allows for identification of trends. For exactly the same purposes, we can do this across larger sets of data in the database itself leveraging its ability to process more complex code designs than otherwise practical in Excel. Further, it is possible to perform an aggregation function upon the data. In my example, I have an employee table where you will see that we have monthly salary and department data stored. Employee names and departments listed are meaningless for demonstration purposes. Following is the employee table, which we are going to pull the salary of each individual, and the department number that they are assigned. I minimized most of the unnecessary columns to show what we are really need. See Figure 1.

Figure 1 Employee Table

In the following sample script, I illustrate one way to write a very simple Pivot Clause. See Figure 2, Simple Pivot Clause.

Figure 2 Simple Pivot Clause

I am using a vertical format in my script strictly for illustrative purposes so you can follow my code and bracketing structure as easily as possible. The results produce cells that show the total salaries summed under each department in one row. See Figure 3.

Figure 3 Pivot Clause output

Unpivot

In preparation for this portion, I created a view we will query so we can unpivot the data shown in Figure 3. Here is one way to write a script to unpivot the data. The method I chose to unpivot is in the following script. See Figure 4.

Figure 4 UnPivot Clause

The SQL script results are much different than what we created in the Pivot Clause earlier. Now we have a vertical output listing each department in its own row with their adjacent salary totals. For organizational purposes, I then ordered the data in descending salary order. The output is shown in Figure 5.

Figure 5 Unpivot Clause output.

Depending on the user needs for information, you can greatly simplify the user experience using the Pivot and UnPivot Clauses in SQL. I hope this helps your understanding of Pivot and UnPivot in SQL coding projects you are called on creating in the future.

Video Presentation

Insightfully yours,

Robert Majdak Sr., Co-Founder

Crystal Majdak, Co-Founder