Wrong keys are re-created

[MaiklT]

In a clean DNN 9.1.1 installation, I find the following foreign keys:

• Table EventLog
  • FK_EventLog_EventLogConfig
  • FK_EventLog_EventLogTypes
• Table ExceptionEvents
  • FK_ExceptionEvents_LogEventID

After running the Stored Procedure ICG_SJ_TruncateEventLog (for the Clear Admin Logs job), the keys are gone and new keys appear - or should I say old keys, I guess this has to do something with older DNN versions or maybe with a turbo script installation or whatever - only a guess)

• Table EventLog
  • FK_EventLog_Exceptions
• Table ExceptionEvents
  • FK_ExceptionEvents_EventLog

This should not happen, the keys should be the same after executing the procedure than they were before.

Here is a script that should do the job. It creates the DROP and CREATE command for each foreign key in the tables EventLog, ExceptionEvents and Exceptions and stores these in a table variable. Then it executes the DROP commands, truncates the tables and executes the CREATE commands.

DECLARE @DropCreate TABLE
(
   ConstraintName nvarchar(100),
   DropScript nvarchar(MAX),
   CreateScript nvarchar(MAX)
)

-- DROP COMMANDS
INSERT INTO @DropCreate (ConstraintName, DropScript)
SELECT
   fk.[name],
   N'ALTER TABLE ' + QUOTENAME(cs.[name]) + '.' + QUOTENAME(ct.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(fk.[name])
FROM
   sys.foreign_keys AS fk
   INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
   INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE
   cs.name = SUBSTRING('{databaseOwner}', 1, LEN('{databaseOwner}') -1)
   AND ct.name IN('EventLog','ExceptionEvents','Exceptions')

-- CREATE COMMANDS
UPDATE @DropCreate
SET
   CreateScript = (SELECT N'ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) +
      ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
      -- get all the columns in the constraint table
      FROM
         sys.columns AS c 
         INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.[object_id]
      WHERE
         fkc.constraint_object_id = fk.[object_id]
      ORDER
         BY fkc.constraint_column_id 
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name) + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
         -- get all the referenced columns
         FROM
            sys.columns AS c 
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.[object_id]
         WHERE
            fkc.constraint_object_id = fk.[object_id]
            AND fk.[name] = ConstraintName
         ORDER BY
            fkc.constraint_column_id 
         FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ')' +
            CASE WHEN fk.delete_referential_action > 0 THEN ' ON DELETE ' + fk.delete_referential_action_desc ELSE '' END +
            CASE WHEN fk.update_referential_action > 0 THEN ' ON UPDATE ' + fk.update_referential_action_desc ELSE '' END
         FROM
            sys.foreign_keys AS fk INNER JOIN sys.tables AS rt ON fk.referenced_object_id = rt.[object_id]
            INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id] INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
            INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
         WHERE
            rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0
            AND fk.[name] = ConstraintName)
   
DECLARE @Script nvarchar(max)
DECLARE cDrop CURSOR FOR SELECT DropScript FROM @DropCreate
OPEN cDrop
FETCH NEXT FROM cDrop INTO @Script
WHILE @@FETCH_STATUS = 0 BEGIN
   EXECUTE @Script
   FETCH NEXT FROM cDrop INTO @Script
END

CLOSE cDrop
DEALLOCATE cDrop

TRUNCATE TABLE {databaseOwner}{objectQualifier}Exceptions
TRUNCATE TABLE {databaseOwner}{objectQualifier}ExceptionEvents
TRUNCATE TABLE {databaseOwner}{objectQualifier}EventLog

DECLARE cCreate CURSOR FOR SELECT CreateScript FROM @DropCreate
OPEN cCreate
FETCH NEXT FROM cCreate INTO @Script
WHILE @@FETCH_STATUS = 0 BEGIN
   EXECUTE @Script
   FETCH NEXT FROM cCreate INTO @Script
END

CLOSE cCreate
DEALLOCATE cCreate

[david-poindexter]

Hmm...interesting! @MaiklT do you plan to submit a PR for this?

[MaiklT]

No. The script above is the pull request :-)

[mitchelsellers]

Thanks for this, it was not noticed and this solution appears to be better. I’ll get this tested and released