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
Function GetPKColName(ByVal strTableName As String) As String
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.
Public Class OracleDictionaryInfo
Implements IDictionaryInfo
Public Function GetPKName(ByVal strTableName As String) _
As String Implements IDictionaryInfo.GetPKColName
strTableName = strTableName.ToUpper
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
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.
Public Class GenScriptCore
Private iDic As IDictionaryInfo
Private Sub New()
End Sub
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)
End Function
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
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)
Dim lstFKTableFKCol = iDic.GetFKReferences(strTableName)
For Each kv In lstFKTableFKCol
sb.AppendLine(gs(kv.Key, kv.Value, strNextLevelFilter))
Next
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.
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.