… 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.

1. 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.
2. 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.
3. 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.
4. 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