Click here to Skip to main content
Click here to Skip to main content
Go to top

A Small Utility to Generate Cascading Delete SQL Script in Oracle

, 22 Feb 2011
Rate this:
Please Sign up or sign in to vote.
The article is to introduce a utility to generate cascading delete script for an Oracle table.

Introduction

The article is to share a small utility to generate cascading delete SQL Script in Oracle and to introduce how it works.

Background

Recently I was doing data fixes for my client, which required to delete data in tens of tables. There tables have reference tables, which have reference tables, which have reference tables ... To delete a table, I had to delete its reference tables first, to delete its reference tables, I had to delete the reference tables' reference tables first and so on. In one case, there are six levels deep and 220 tables to delete first in strict order. I created the utility to generate the cascading delete script to save me from the tedious and error-prone job. I post it in CodeProject so that others may use it since I got a lot of help from the site. The utility supports Oracle database now. But it can be extended to support SQLServer. The source code is provided so that others may extend it.

Using the Code

The code structure of the utility consists of three parts. The bottom part is to get the database dictionary information like primary key and reference table. The middle part is to implement the core logic to generate script. The upper part is the UI, a windows application interface which lets the user generate script. The utility was implemented with VB.NET and Visual Studio 2010.

Let me introduce from the bottom part. In the bottom part, an interface was introduced so that it can be extended to support different databases.

Public Interface IDictionaryInfo

    ' Get Primary Key of a table
    Function GetPKColName(ByVal strTableName As String) As String

    ' Get foreign key reference table and the foreign key column in that table
    '   A list of KeyValuePair rather than a dictionary is used as
    '   return type since one reference table may have 1+ reference column.
    Function GetFKReferences(ByVal strTableName As String) _
    	As List(Of KeyValuePair(Of String, String))

End Interface

Here is the implementation of the interface to support Oracle database.

' the implementation of the IDictionaryInfo for oracle
Public Class OracleDictionaryInfo
    Implements IDictionaryInfo

    ' get the primary key for a table
    Public Function GetPKName(ByVal strTableName As String) _
    	As String Implements IDictionaryInfo.GetPKColName
        strTableName = strTableName.ToUpper		'Oracle tables are saved
        				' in uppercase in Oracle dictionary.

        'SQL to retrieve oracle dictionary to get primary key
        Dim strSQl As String = String.Format_
        	("SELECT cols.column_name FROM user_constraints cons, _
        	user_cons_columns cols WHERE cols.table_name = '{0}' _
        	AND cons.constraint_type = 'P' AND cons.constraint_name = _
        	cols.constraint_name", strTableName)

        Dim dt = GetDataTable(strSQl)

        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)(0)
        Else
            Return String.Empty
        End If
    End Function


    ' get the foreign key tables and its reference columns
    Public Function GetFKReferences(ByVal strTableName As String) _
    As System.Collections.Generic.List(Of _
    System.Collections.Generic.KeyValuePair(Of String, String)) _
    Implements IDictionaryInfo.GetFKReferences
        strTableName = strTableName.ToUpper

        Dim strSQl As String = String.Format_
        ("select C.TABLE_NAME, C.COLUMN_NAME from user_cons_columns _
        c where c.CONSTRAINT_NAME IN ( Select constraint_name from_
        (user_constraints) where constraint_type='R'  and _
        r_constraint_name in (select constraint_name from user_constraints _
        where constraint_type in ('P','U') and table_name='{0}'))", _
        strTableName)

        Dim dt = GetDataTable(strSQl)

        Dim tnq = From dr In dt.Rows Select New KeyValuePair_
        (Of String, String)(CType(dr(0), String), CType(dr(1), String))
        Return tnq.ToList()
    End Function

End Class

The middle part collects database dictionary information of primary keys and references. It calls an internal recursive function to generate the delete scripts in a cascading way.

' Core class to generate script for the table
Public Class GenScriptCore

    Private iDic As IDictionaryInfo

    Private Sub New()   ' no instance is allowed
    End Sub

    ' Main function to generate script.  Prepare database type, _
    connection string and table name to call the function
    Public Shared Function GenScript(ByVal enumDB As DB, _
    ByVal strConnection As String, ByVal strTableName As String) As String
        Dim oc As New GenScriptCore

        Select Case [enumDB]
            Case DB.Oracle
                oc.iDic = New OracleDictionaryInfo(strConnection)
            Case DB.SQlServer
                Throw New Exception("SQL server is not supported yet. _
                But you can implement it by implementing the interface of _
                IDictionaryInfo for SQL Server")
        End Select

        Dim strPKCol = oc.iDic.GetPKColName(strTableName)

        If String.IsNullOrEmpty(strPKCol) Then
            Throw New Exception(String.Format("table '{0}' _
            does not exist or has no primary key / unique key", strTableName))
        End If

        Return oc.gs(strTableName, strPKCol) ' call the main method
    End Function

	' Core function to generate script though recursive calling
    Private Function gs(ByVal strTableName As String, _
    ByVal strFilterColumn As String, _
    Optional ByVal strInFilter As String = Nothing) As String

        Dim sb As New System.Text.StringBuilder
        Dim strPKCol = iDic.GetPKColName(strTableName)

        If String.IsNullOrEmpty(strPKCol) Then
            Throw New Exception(String.Format("table '{0}' _
            has no primary key / unique key", strTableName))
        End If

        ' define where clause for the reference tables
        Dim strNextLevelWhereClause As String
        If Not String.IsNullOrEmpty(strInFilter) Then
            strNextLevelWhereClause = String.Format("{0} _
            in ({1})", strFilterColumn, strInFilter)
        Else
            strNextLevelWhereClause = String.Format("{0} = _
            p_{0}", strFilterColumn)
        End If

        Dim strNextLevelFilter = String.Format("Select {0} from _
        {1} where {2}", strPKCol, strTableName, strNextLevelWhereClause)

        ' find the foreign key reference tables and delete them first
        Dim lstFKTableFKCol = iDic.GetFKReferences(strTableName)
        For Each kv In lstFKTableFKCol
            sb.AppendLine(gs(kv.Key, kv.Value, strNextLevelFilter))
        Next

        '  delete itself
        Dim strDeleteWhereClause As String
        If Not String.IsNullOrEmpty(strInFilter) Then
            strDeleteWhereClause = String.Format_
            ("{0} in ({1})", strFilterColumn, strInFilter)
        Else
            strDeleteWhereClause = String.Format_
            ("{0} = p_{0}", strFilterColumn)
        End If

        sb.AppendFormat("delete {0} where {1};", _
        strTableName, strDeleteWhereClause)
        sb.AppendLine()

        Return sb.ToString
    End Function

End Class

The UI collects user input of database type, connection string and table name. It verifies the input and calls the middle layer to generate the script, which is displayed inside the window and at the same time is copied to the clipboard. If the app throws an exception, UI will show the error message in red.

Click to enlarge image

Points of Interest

The small utility helped me a lot. I wish the small tool can help other people.

History

  • Initial version: Feb 17, 2011

The author, Ronald Cao, is a senior .NET Developer in TP Systems Co.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralTSQLDictionaryInfo PinmemberRon Schuler14-Mar-11 12:59 
You'll need to change the interface to also get a list of "self referencing foreign keys" since they cause your app to recurse into a stack overflow exception.
Here is the TSQL class for SQL Server
Imports System.Linq
Imports System.Data.SqlClient
 
''' <summary>
''' the implementation of the IDictionaryInfo for SQL Server 
''' </summary>
''' <remarks></remarks>
Public Class TSQLDictionaryInfo
    Implements IDictionaryInfo
 
    Private strConnection As String = ""
 
    Public Sub New(ByRef strConnection As String)
        Me.strConnection = strConnection
    End Sub
 

    Public Function GetPKName(ByVal strTableName As String) As String Implements IDictionaryInfo.GetPKColName
        strTableName = strTableName.ToUpper
 
        Dim strSQl As String = String.Format("SELECT ccu.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.CONSTRAINT_TYPE = 'Primary Key' and ccu.TABLE_NAME = '{0}'", strTableName)
 
        Dim dt = GetDataTable(strSQl)
 
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)(0)
        Else
            Return String.Empty
        End If
    End Function
 

 
    Public Function GetFKReferences(ByVal strTableName As String) As System.Collections.Generic.List(Of System.Collections.Generic.KeyValuePair(Of String, String)) Implements IDictionaryInfo.GetFKReferences
        strTableName = strTableName.ToUpper
 
        Dim strSQl As String = String.Format("SELECT FK.TABLE_NAME, CU.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME where PK.TABLE_NAME = '{0}' and FK.TABLE_NAME <> '{0}'", strTableName)
 
        Dim dt = GetDataTable(strSQl)
 
        Dim tnq1 = From dr In dt.Rows Select New KeyValuePair(Of String, String)(CType(dr(0), String), CType(dr(1), String))
        Return tnq1.ToList()
 
    End Function
 

#Region "private method"
 

    ''' <summary>
    ''' Get a dataset for a SQL select script 
    ''' </summary>
    ''' <param name="strSelectCMD"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDataSet(ByVal strSelectCMD) As DataSet
        Dim objConnection As SqlClient.SqlConnection = Nothing
 
        Try
            objConnection = New SqlClient.SqlConnection(Me.strConnection)
            objConnection.Open()
            Dim objDA As New SqlDataAdapter(strSelectCMD, objConnection)
 
            Dim ds = New DataSet
            objDA.Fill(ds)
            objConnection.Close()
            Return ds
        Catch
            Throw
        Finally
            If Not IsNothing(objConnection) AndAlso objConnection.State = ConnectionState.Open Then
                objConnection.Close()
            End If
        End Try
 
        Return Nothing
    End Function
 

    ''' <summary>
    ''' Get a datatable for the SQL select script.
    ''' </summary>
    ''' <param name="strSelectCMD"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetDataTable(ByVal strSelectCMD) As DataTable
        Return GetDataSet(strSelectCMD).Tables(0)
    End Function
 
#End Region
 

 
End Class

Generalthanks for sharing PinmemberPranay Rana24-Feb-11 2:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 23 Feb 2011
Article Copyright 2011 by ronald.cao@gmail.com
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid