Introduction
I was providing consulting services, developing an application in C# and .NET 2.0 with a SQL Server 2000 database. The application framework was based on a custom code-generator, which created SQL scripts, business objects, and UI code. Because the previous consultant who designed the code-generator was no longer available, time constraints restricted us from discovering and modifying the generator, leading me to take a different approach to my solutions.
In this article, I describe how I created the ability to perform deletes. The framework did not include proper scripts for cleaning up and deleting records, and for reasons beyond the discussion of this article, I chose to do all of the constraint checks and deletes from a stored procedure, and return an error message if any constraint violations would occur from a delete.
SQL Source Code
Adding a DROP and ADD command at the beginning of the script makes it easy to update this stored procedure without having to create an accompanying ALTER script.
if exists (select * from dbo.sysobjects where
id = object_id(N'[dbo].[DeleteByTableAndId]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteByTableAndId]
GO
Adding a DROP and ADD command for the custom error message that will be raised if constraints will be violated with a deletion ensures that the error message will fit the way we will use it. Notice the ‘%s’ which acts as a string place holder for the error message.
IF(EXISTS (SELECT * FROM master.dbo.sysmessages WHERE error = 50001))
BEGIN
EXECUTE sp_dropmessage 50001;
END
GO
EXECUTE sp_addmessage 50001, 18, '%s';
GO
Included pieces of the system stored procedure sp_fkeys, and made the input parameters for table name, primary key ID, and bit flags for removing relationships and performing deletes.
CREATE PROCEDURE DeleteByTableAndId(
@pktable_name sysname,
@object_id int,
@remove_relationships bit=0,
@perform_delete bit=0)
Included the field in #fkeysout that indicates if the foreign key is nullable.
create table #fkeysout(
PKTABLE_QUALIFIER sysname collate database_default NULL,
PKTABLE_OWNER sysname collate database_default NULL,
PKTABLE_NAME sysname collate database_default NOT NULL,
PKCOLUMN_NAME sysname collate database_default NOT NULL,
FKTABLE_QUALIFIER sysname collate database_default NULL,
FKTABLE_OWNER sysname collate database_default NULL,
FKTABLE_NAME sysname collate database_default NOT NULL,
FKCOLUMN_NAME sysname collate database_default NOT NULL,
KEY_SEQ smallint NOT NULL,
UPDATE_RULE smallint NULL,
DELETE_RULE smallint NULL,
FK_NAME sysname collate database_default NULL,
PK_NAME sysname collate database_default NULL,
DEFERRABILITY smallint null,
ISNULLABLE smallint null)
insert into #fkeysout
select
PKTABLE_QUALIFIER = convert(sysname,db_name()),
PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid)),
PKTABLE_NAME = convert(sysname,o1.name),
PKCOLUMN_NAME = convert(sysname,c1.name),
FKTABLE_QUALIFIER = convert(sysname,db_name()),
FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid)),
FKTABLE_NAME = convert(sysname,o2.name),
FKCOLUMN_NAME = convert(sysname,c2.name),
KEY_SEQ,
UPDATE_RULE = CASE WHEN (ObjectProperty(fk_id,
'CnstIsUpdateCascade')=1) THEN
convert(smallint,0) ELSE convert(smallint,1) END,
DELETE_RULE = CASE WHEN (ObjectProperty(fk_id,
'CnstIsDeleteCascade')=1) THEN
convert(smallint,0) ELSE convert(smallint,1) END,
FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),
PK_NAME,
DEFERRABILITY = 7,
ISNULLABLE = convert(smallint,c2.isnullable)
from #fkeys f,
sysobjects o1, sysobjects o2,
syscolumns c1, syscolumns c2
where o1.id = f.pktable_id
AND o2.id = f.fktable_id
AND c1.id = f.pktable_id
AND c2.id = f.fktable_id
AND c1.colid = f.pkcolid
AND c2.colid = f.fkcolid
The first section of the custom code contains declarations for the SQL execute string and the string that will hold return values from sp_executesql.
DECLARE @sql_string nvarchar(4000)
SET @sql_string = ''
DECLARE @return NVARCHAR(4000)
SET @return = ''
If the table being deleted has no constraints, skip to DeleteLabel.
IF(NOT EXISTS (SELECT * FROM #fkeysout))
GOTO DeleteLabel
If the parameter was not set to nullify the constraint, check all of the relationships. Otherwise, only check the non-nullable relationships. The conditional clause is basically a SQL string builder that selects from #fkeysout, and keeps us from using a looping cursor. This will produce an inline SQL statement that assigns the result to the variable declared above. The end result will be a comma-separated string like this:
,TABLE01(5),TABLE02(12),TABLE03(1)
IF(@remove_relationships = 0)
SELECT @sql_string = @sql_string + ' ISNULL((SELECT '',' +
FKTABLE_NAME + '('' + CONVERT(varchar, COUNT(*)) +
'')'' AS [TABLE] FROM [' + FKTABLE_NAME +
'] WHERE [' + FKCOLUMN_NAME + '] = ' +
CONVERT(varchar,@object_id) + ' GROUP BY [' +
FKCOLUMN_NAME + ']), '''')+'
FROM #fkeysout
ELSE
SELECT @sql_string = @sql_string + ' ISNULL((SELECT '',' +
FKTABLE_NAME + '('' + CONVERT(varchar, COUNT(*)) +
'')'' AS [TABLE] FROM [' +
FKTABLE_NAME + '] WHERE [' + FKCOLUMN_NAME +
'] = ' + CONVERT(varchar,@object_id) + ' GROUP BY [' +
FKCOLUMN_NAME + ']), '''')+'
FROM #fkeysout
WHERE ISNULLABLE = 0
SET @sql_string = 'SELECT' + @sql_string
SET @sql_string = LEFT(@sql_string, LEN(@sql_string)-1)
SET @sql_string = 'SELECT @RETURN_OUT = (' + @sql_string + ');'
EXECUTE sp_executesql @sql_string,
N'@RETURN_OUT NVARCHAR(4000) OUTPUT',
@return_out = @return OUTPUT
If a return exists, then irresolvable constraints were found. Format the string to include the primary key table name and the result string, removing the beginning comma.
Note: SQL Server 2000 has a hard length restriction of 399 characters for error messages, so I resorted away from using XML. Ideally, I would prefer to short-circuit the process and return XML to the caller. If XML existed, the caller would process this as if an error had been raised.
The existing result will look like this:
{TABLEPK}TABLE01(5),TABLE02(12),TABLE03(1)
IF(LEN(@return)>0)
BEGIN
SET @return = '{' + @pktable_name + '}' +
RIGHT(@return, LEN(@return)-1)
RAISERROR (50001,18,1,@return)
END
The first labeled section cleans up the relationships by nulling-out the fields. Here, I use the same SQL string builder approach.
ResolveLabel:
IF(@remove_relationships = 1 AND LEN(@return)=0)
BEGIN
SET @sql_string = ''
SELECT @sql_string = @sql_string + ' UPDATE [' +
FKTABLE_NAME + '] SET [' +
FKCOLUMN_NAME + '] = NULL WHERE ['
+ FKCOLUMN_NAME + '] = ' +
CONVERT(nvarchar,@object_id) + ';'
FROM #fkeysout
WHERE ISNULLABLE = 1
EXECUTE(@sql_string)
END
The final labeled section is for deleting the actual record from the primary key table. First, check to see if the table selected has a composite primary key, and return an error if it does, since this stored procedure is only designed to manage tables with constraints. Then, retrieve the primary key column name from the metadata. Finally, build and perform the SQL statement.
DeleteLabel:
IF(@perform_delete = 1 AND LEN(@return)=0)
BEGIN <CODE lang=sql>DELETE</CODE>
SET @sql_string = ''
SET @return = ''
DECLARE @return_count int
SET @return_count = 0
SET @sql_string = 'SELECT @RETURN_COUNT_OUT =
(SELECT COUNT(*) FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = ''' + @pktable_name +
''' AND CHARINDEX(''PK_'',CONSTRAINT_NAME)>0)'
EXECUTE sp_executesql @sql_string,
N'@RETURN_COUNT_OUT INT OUTPUT',
@return_count_out = @return_count OUTPUT
IF(@return_count <> 1)
BEGIN
RAISERROR('Cannot delete this record because it
does not have only one identifier.',18,1);
END
ELSE
BEGIN
SET @sql_string = 'SELECT @RETURN_OUT = (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = ''' + @pktable_name +
''' AND CHARINDEX(''PK_'',CONSTRAINT_NAME)>0)'
EXECUTE sp_executesql @sql_string, N'@RETURN_OUT
NVARCHAR(4000) OUTPUT', @return_out = @return OUTPUT
SET @sql_string = 'DELETE [' + @pktable_name + '] WHERE [' +
@return + '] = ' + CONVERT(nvarchar,@object_id) + ';'
EXECUTE sp_executesql @sql_string
END
END
A few improvements could be adding the ability to perform cascaded DELETEs and return XML instead of an error message. I tend to stay away from cascading DELETEs, because they can be dangerous and might spawn an application time-out.
Error Class
I also included a complimenting error class coded in C# 2.0 that handles all of the processing for the custom SQL error raised.
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
namespace Acme.Common.Exceptions
{
public class ConstraintException : System.Exception
{
public ConstraintException(string message)
: base(FixMessage(message))
{
}
public ConstraintException(string message,
System.Exception ex)
: base(FixMessage(message), ex)
{
}
private static string FixMessage(string msg)
{
try
{
if (msg != string.Empty)
{
int index = msg.LastIndexOf(")");
if (index != msg.Length - 1)
{
msg = msg.Substring(0,
msg.Length - index + 1);
}
}
}
catch
{
msg = string.Empty;
}
return msg;
}
public string Primary
{
get
{
try
{
Regex regex = new Regex(@"\{(?<primary>" +
@".*)\}(?<relationships>.*)");
return regex.Replace(Message, "${primary}");
}
catch
{
return string.Empty;
}
}
}
public List<ConstraintRelationship> Relationships
{
get
{
List<ConstraintRelationship> list =
new List<ConstraintRelationship>();
try
{
Regex regex = new Regex(@"\{(?<primary>" +
@".*)\}(?<relationships>.*)");
string[] relationships = regex.Replace(Message,
"${relationships}").Split(',');
for (int i = 0; i < relationships.Length; i++)
{
Regex regex2 = new Regex(@"(?<relationship>" +
@".*)\((?<count>.[0-9]*)\)");
string name = regex2.Replace(relationships[i],
"${relationship}");
string count = regex2.Replace(relationships[i],
"${count}");
list.Add(new ConstraintRelationship(name,
Convert.ToInt32(count)));
}
list.Sort();
}
catch (Exception ex)
{ }
return list;
}
}
public class ConstraintRelationship : IComparable
{
public string Name;
public int Count;
public ConstraintRelationship(string name, int count)
{
Name = name;
Count = count;
}
#region IComparable Members
public int CompareTo(object obj)
{
if (obj is ConstraintRelationship)
{
ConstraintRelationship cr = (ConstraintRelationship)obj;
return Name.CompareTo(cr.Name);
}
throw new ArgumentException("object is " +
"not a Constraint Relationship");
}
#endregion
}
}
}
