… to avoid “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”
In most cases if you need to support linking of more than one ID from one table into another table, you would use an intermediary table, this allows any number of links to be established and details of each link can be supplemented with additional details.
However when maintaining and upgrading an existing database that has been around for many years, or even designing a new one, were there will only ever be a need to link in a specific number of IDs, it may be the case that the IDs are placed within the other table directly.
Doing this prevents the ability for SQL to use the ON DELETE CASCADE and ON DELETE SET NULL from working.
Indeed even if there is only the one ID linked in, but there is more than one round table link you cannot have multiple ON DELETE CASCADE foreign keys established between the tables because it will also fail. Both these examples give the error “Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths”.
I recently embarked on upgrading an existing database to include foreign keys relationships (previously controlled by software only), so that we could introduce full support for merge replication.
The database includes over 360 tables with around 860 relationships to be establish between tables, it is used by over 4500 users in a large number of organisations, with around 70 modules (or applications) accessing the data.
As the applications have been designed and written over the past 10 years by several developers there is no way to guarantee that all the relationships between tables are being enforced correctly… Leaving aside the creation of the FKs and the verification of existing data for another blog, I needed a way to maintain compatibility with all the applications currently using the database. The only way to do this while properly enforcing the foreign keys would be to delete or set to NULL the appropriate linked data on deletion of a row, rather than rely on the application to perform the action.
Having searched for quite a while I found that there was many examples of people recommending using an INSTEAD OF DELETE TRIGGER, to accomplish it, though I could not find any examples. After searching for some time the only examples I found contain hard coded references to linked tables. This is no way ideal and would be a nightmare to maintain with over 860 foreign key links between over 360 tables, which are added to and modified on a regular basis as the applications continue to grow.
Anyway to cut a long story short and get to the code I wrote a TRIGGER that would satisfy my needs, based on a few assumptions.
- If a Foreign Key column is nullable I am assuming it is not dependant on the Primary Key data and should be set to NULL rather than deleted.
- If the Foreign Key does not support NULLs then the data must be dependant on a valid link and will be deleted… obviously in this case I am assuming that all of the FKs that do not support NULLs are not to be set to a default value from the PK table.
- Within the TRIGGER I am will be checking if the FK constraint has a referential action, is disable, is trusted (enforced) and also isn’t a link back to the PK table itself.
- The Trigger is designed to fail if fired against a table that has a multi-column Primary Key.
It is worth noting when using a trigger it can be run on multiple rows and is not run for each row deleted, so must handle this
Now I am a bit short of time tonight so I am just going to just show the source of what I have done and assume you will be able to interpret it….
The trigger will cycle through all Foreign keys based on the tables name and the primary key column name. It then deletes or updates the dependant data as appropriate, as it does so (assuming the same trigger is on the dependant tables) if the dependant tables have an update or delete trigger on them it will in turn trigger that causing a cascade effect… As a result of this it could potentially result in a long loop so needs to be used with caution and the database’s foreign keys need to be established carefully.
You should not use this trigger if you are unsure, and I bear no responsibility for any data that is lost as a result of it’s usage! The trigger is based on T-SQL for MS SQL Server 2005, INSTEAD OF DELETE does not work in MS SQL 2000 and I have not tested it on MS SQL 2008.
The trigger looks something like the following; it is based on a table called [Table_1] with a Primary Key column that is either an integer or varchar called [ID], I will be adding it to all tables I’ll be writing a script that just runs through all the tables adding it: –
Code revised 31st Jan. 2009
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIG_PK_Table_1_Delete_FKs]’))
DROP TRIGGER [dbo].[TRIG_PK_Table_1_Delete_FKs]
GO
/**
CODED BY: Paul Bunting (http://www.paulbunting.com/)
Created: 23/01/2009
Modified: 31/01/2009
**/
CREATE TRIGGER TRIG_PK_Table_1_Delete_FKs ON dbo.Table_1
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
PRINT ‘***************************************************’
PRINT ‘RUNNING – TRIG_PK_Table_1_Delete_FKs’
— before doing anything lets check we
— actually have something to delete!
DECLARE @Counter_DELETED_ROWS INT
SELECT @Counter_DELETED_ROWS = COUNT([EQUIPMENTID]) FROM DELETED
IF (@Counter_DELETED_ROWS <= 0)
BEGIN
PRINT ”
PRINT ‘0 records deleted’
PRINT ”
END
ELSE
BEGIN
— 1) we only wish to deal with tables that have one Primary Key…
— so lets check that this table has only one Primary Key
DECLARE @Counter_PKs INT
SELECT @Counter_PKs = COUNT(Col.Column_Name) FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
ON Col.Constraint_Name = Tab.Constraint_Name AND
Col.Table_Name = Tab.Table_Name
WHERE Tab.Constraint_Type = ‘PRIMARY KEY’ AND
Col.Table_Name = ‘Table_1’
— 2) Verify only 1 found
PRINT ‘ Found ‘ + CONVERT(VARCHAR(255), @Counter_PKs) +
‘ primary keys in table’
IF (NOT (@Counter_PKs = 1))
BEGIN
— Lets throw an error if this trigger has been established
— against a table with more than one Primary Key, could
— alter this later to handle them but no requirement to
— do so at the moment
DECLARE @tn varchar(4000)
SELECT @tn = object_name(parent_obj)
FROM sysobjects WHERE id = @@procid
SET @tn = ‘Casscade deletes not allowed for this ‘ +
‘table due to multiple primary key columns: ‘ + @tn
PRINT @tn
IF EXISTS(SELECT TOP(1) * from deleted)
RAISERROR (@tn, 16, 1)
END
ELSE
BEGIN
— 3) Lets get all the Foreign Keys assosiated with this table
DECLARE @ForeignKeys TABLE (TableTempID bigint
IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Name] varchar(2000),[OBJECT_ID] int, [is_disabled] bit,
[is_not_for_replication] bit,
[delete_referential_action] int,
[update_referential_action] int,
[Fk_table_name] varchar(2000),
[Fk_table_schema] varchar(2000),
[Pk_table_name] varchar(2000),
[Pk_table_schema] varchar(2000),
[Fk_col_name] varchar(2000),
[Pk_col_name] varchar(2000),
[constraint_column_id] int,
[is_not_trusted] bit)
INSERT INTO @ForeignKeys
( [Name], [OBJECT_ID], [is_disabled],
[is_not_for_replication], [delete_referential_action],
[update_referential_action], [Fk_table_name],
[Fk_table_schema], [Pk_table_name], [Pk_table_schema],
[Fk_col_name], [Pk_col_name], [constraint_column_id],
[is_not_trusted] )
( select Fk.name, Fk.object_id, Fk.is_disabled,
Fk.is_not_for_replication, Fk.delete_referential_action,
Fk.update_referential_action,
object_name(Fk.parent_object_id) as Fk_table_name,
schema_name(Fk.schema_id) as Fk_table_schema,
TbR.name as Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema,
col_name(Fk.parent_object_id,
Fk_Cl.parent_column_id) as Fk_col_name,
col_name(Fk.referenced_object_id,
Fk_Cl.referenced_column_id) as Pk_col_name,
Fk_Cl.constraint_column_id,
Fk.is_not_trusted
from sys.foreign_keys Fk
left outer join sys.tables TbR
on TbR.object_id = Fk.referenced_object_id
inner join sys.foreign_key_columns Fk_Cl
on Fk_Cl.constraint_object_id = Fk.object_id
where
( ([TbR].[name] = ‘Table_1’) AND
(col_name(Fk.referenced_object_id,
Fk_Cl.referenced_column_id) = ‘ID’) AND
([Fk].[delete_referential_action] = 0) AND
([Fk].[is_disabled] = 0) AND
([Fk].[is_not_trusted] = 0) ) )
— 4) Lets see how many Foreign Keys we are dealing with
DECLARE @Counter bigint
DECLARE @MaxTableCount BIGINT
SELECT @MaxTableCount = max(TableTempID) from @ForeignKeys
SET @Counter = 1
PRINT ‘ Found ‘ + CONVERT(VARCHAR(255), @MaxTableCount) +
‘ foreign keys links for this table’
DECLARE @Fk_table_name VARCHAR(2000)
DECLARE @Pk_table_name VARCHAR(2000)
DECLARE @Fk_col_name VARCHAR(2000)
DECLARE @Pk_col_name VARCHAR(2000)
DECLARE @is_not_trusted BIT
DECLARE @is_disabled BIT
DECLARE @delete_referential_action INT
DECLARE @Fk_col_IsNullable BIT
DECLARE @sqlQuery nVarchar(4000)
— 5) Lets cycle through all the Foreign Keys
WHILE (@Counter <= @MaxTableCount)
BEGIN
SELECT @Fk_table_name = ltrim(rtrim(Fk_table_name))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @Pk_table_name = ltrim(rtrim(Pk_table_name))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @Fk_col_name = ltrim(rtrim(Fk_col_name))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @Pk_col_name = ltrim(rtrim(Pk_col_name))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @is_not_trusted = ltrim(rtrim(is_not_trusted))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @is_disabled = ltrim(rtrim(is_disabled))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @delete_referential_action =
ltrim(rtrim(delete_referential_action))
FROM @ForeignKeys WHERE TableTempID = @Counter
SELECT @Fk_col_IsNullable = c.IsNullable
FROM syscolumns c WHERE c.id =
OBJECT_ID(@Fk_table_name) AND
c.NAME = @Fk_col_name
PRINT ‘—————————————‘
PRINT ‘ Processing key ‘ +
CONVERT(VARCHAR(255), @Counter) + ‘ of ‘ +
CONVERT(VARCHAR(255), @MaxTableCount)
PRINT ‘ @Fk_table_name "’ +
CONVERT(VARCHAR(255), @Fk_table_name) +
‘" ~ @Fk_col_name – "’ +
CONVERT(VARCHAR(255), @Fk_col_name) + ‘"’
PRINT ”
— 6) Lets verify that there is no CASCADE or
— UPDATE already on the Key link and that
— it is not a system table… also
— need to verify that the Foreign Key Table
— is not the same as the current table and
— that Key should be enforced.
IF (
(@delete_referential_action = 0) AND
(@is_not_trusted = 0) AND
(@is_disabled = 0) AND
(@Fk_table_name <> ‘dtproperties’) AND
(@Fk_table_name <> ‘sysdiagrams’) AND
(NOT (@Fk_table_name = @Pk_table_name))
)
BEGIN
— 7) Because we need to execute deletes with
— a "EXECUTE sp_executesql…" statement the
— DELETED table is not available so we must
— cycle through it in case it is a batch
— delete.
—
— We convert the values to Varchar here to
— account for PKs that are text and integer,
— plus we need it as text later
—
— This is because we do not know what all
— the FK column names will be so we need
— to build the query
DECLARE @DELETED_ROWS TABLE
(TableTempID bigint IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
[ValueINNER] varchar(4000))
INSERT INTO @DELETED_ROWS
( [ValueINNER] )
( SELECT CONVERT(VARCHAR(4000), [ID])
from DELETED )
DECLARE @CounterINNER bigint
DECLARE @MaxTableCountINNER BIGINT
DECLARE @ValueINNER VARCHAR(4000)
SELECT @MaxTableCountINNER = max(TableTempID)
from @DELETED_ROWS
SET @CounterINNER = 1
— 8) Lets cycle through all the ROWS to be deleted
WHILE (@CounterINNER <= @MaxTableCountINNER)
BEGIN
SELECT @ValueINNER =
ltrim(rtrim([ValueINNER]))
FROM @DELETED_ROWS WHERE
TableTempID = @CounterINNER
— 9) Lets see if the Foreign Key
— column allows nulls… we will
— use this to guess if the linked
— table is dependant on the
— link (i.e. a link between
— an email and it’s attachments)
— or if it is only a lookup link
— (i.e. a link between a staff
— member and the email they edited…
— may want to delete the staff
— member but don’t want their
— emails deleted)
PRINT ‘====’
IF (@Fk_col_IsNullable = 1)
BEGIN
— 10a) Set LINKED values to NULL
— (Using the like statement here
— should account for INT and
— VARCHAR PKs)
PRINT ‘ UPDATE [‘ + @Fk_table_name
+ ‘] SET [‘ + @Fk_col_name
+ ‘] = NULL WHERE [‘ + @Fk_col_name
+ ‘] LIKE ”’ + @ValueINNER + ””
SET @sqlQuery = ‘
SET NOCOUNT OFF
UPDATE [‘ + @Fk_table_name + ‘] SET [‘ + @Fk_col_name + ‘] = NULL WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’
SET NOCOUNT ON’
–PRINT @sqlQuery
EXECUTE sp_executesql @sqlQuery
END
ELSE
BEGIN
— 10b) Delete DEPENDANT values
— (Using the like statement
— here should account for INT
— and VARCHAR PKs)
PRINT ‘ DELETE FROM [‘
+ @Fk_table_name + ‘] WHERE [‘
+ @Fk_col_name + ‘] LIKE ”’
+ @ValueINNER + ””
SET @sqlQuery = ‘
SET NOCOUNT OFF
DELETE FROM [‘ + @Fk_table_name + ‘] WHERE [‘ + @Fk_col_name + ‘] LIKE ”’ + @ValueINNER + ”’
SET NOCOUNT ON’
–PRINT @sqlQuery
EXECUTE sp_executesql @sqlQuery
END
PRINT ‘====’
SET @CounterINNER = @CounterINNER + 1
END
END
SET @Counter = @Counter + 1
END
PRINT ‘—————————————‘
— 11) Proceed with the delete…
SET NOCOUNT OFF
DELETE FROM [Table_1]
WHERE [ID] in (SELECT [ID] FROM DELETED)
SET NOCOUNT ON
–PRINT ‘DELETE FROM [Table_1]
— WHERE [ID] in (SELECT [ID] FROM DELETED)’
END
END
PRINT ‘COMPLETED – TRIG_PK_Table_1_Delete_FKs’
PRINT ‘***************************************************’
SET NOCOUNT OFF
END
GO
Paul Bunting • Software Architect 