Will "the Mighty" Strohl

The Power of Foreign Keys in DotNetNuke Modules

When I am developing any product, I try to do my best to limit the amount of time that my customer or staff will have to manage that product.  This can be looked at from one perspective in terms of usability.  I like to think that I keep usability as my #1 priority.  However, another way this comes in to play is with administration under the covers.  There are literally too many ways that you can do that, thus limiting how many I can mention.  So I will just mention the one I intend to talk about, Foreign Keys. 

Foreign Keys are very often a foreign topic to developers.  Most developers know enough about a database to get connected and manage the data that their code affects, but many do not know enough about the ins and outs of things like Foreign Keys and Indexes.  There is a marketplace of consultants that look specifically at things like this.

Example Foreign Key Relationship When you build a module for DotNetNuke®, you usually will end up with a database foot print similar to this (in the simplest approach):

  • company_Tasks
  • company_TaskComments
  • company_TaskTypes

Let’s assume with this example, that the Tasks table holds the following fields:

  • TaskId
  • ModuleId
  • UserId
  • TaskTypeId
  • TaskName

The TaskComments table might have columns like this:

  • CommentId
  • TaskId
  • Comment
  • UserId

Finally, the TaskTypes table might have the following columns:

  • TypeId
  • ModuleId
  • TypeName

With the footprint that I just laid out for you, this would have the illustrated relationship shown in the image on the right.  Initially, this might look exactly what you want.  The ModuleId will be correctly references by tasks, the tasks will correctly reference the TaskTypeId, and so.  But this is not the only thing you need to worry about.

What if the Module is deleted?  Without any plumbing in place, this will result in the Tasks, TaskComments, and TaskTypes being “orphaned.”  Meaning, it is wasted data that is just taking up space.  Your application will never again reference it.  How do we fix that?  Through the Foreign Key.

It is not enough to just specify the Foreign Key.  The Foreign Key allows you to also attach the data record to an event (so to speak).  What we want to happen is make the Tasks and related records get deleted when the module does, and also delete the TaskComments when the individual Tasks are deleted, and so on.

What you need to do is just add a few words to the end of your Foreign Key constraint when it’s created.  Without the new code, your table script might look like this:

IF NOT OBJECT_ID('[dbo].[company_Tasks]') IS NULL
    DROP TABLE [dbo].[company_Tasks]
 
CREATE TABLE [dbo].[company_Tasks] (
    [TaskId] INT IDENTITY(1, 1) NOT NULL 
        CONSTRAINT [PK_company_Tasks] PRIMARY KEY CLUSTERED ON [PRIMARY], 
    [ModuleId] INT NOT NULL 
        CONSTRAINT [FK_company_Tasks_ModuleId] FOREIGN KEY  
        REFERENCES [dbo].[Modules] ([ModuleId]), 
    [UserId] INT NOT NULL 
        CONSTRAINT [FK_company_Tasks_UserId] FOREIGN KEY 
        REFERENCES [dbo].[Users] ([UserID]), 
    [TaskTypeId] INT NOT NULL 
        CONSTRAINT [FK_company_Tasks_TypeId] FOREIGN KEY 
        REFERENCES [dbo].[company_TaskTypes] ([TypeId]), 
    [TaskName] NVARCHAR(50) NOT NULL
)

The first thing the code snippet does is to delete the table if it already exists.  Then, it creates the table, with the Primary Key and Foreign Keys already in place at creation.  Note in the ModuleId, UserId, and TaskTypeId fields, where we see where our code is referencing the keys in other tables.  We can now add the few short words at the end of the Foreign Key statements to make our Foreign Key constraints more useful.

IF NOT OBJECT_ID('[dbo].[company_Tasks]') IS NULL
    DROP TABLE [dbo].[company_Tasks]
 
CREATE TABLE [dbo].[company_Tasks] (
    [TaskId] INT IDENTITY(1, 1) NOT NULL 
        CONSTRAINT [PK_company_Tasks] PRIMARY KEY CLUSTERED ON [PRIMARY], 
    [ModuleId] INT NOT NULL 
        CONSTRAINT [FK_company_Tasks_ModuleId] FOREIGN KEY  
        REFERENCES [dbo].[Modules] ([ModuleId]) ON DELETE CASCADE NOT FOR REPLICATION, 
    [UserId] INT NOT NULL 
        CONSTRAINT [FK_company_Tasks_UserId] FOREIGN KEY 
        REFERENCES [dbo].[Users] ([UserID]) ON DELETE CASCADE NOT FOR REPLICATION, 
    [TaskTypeId] INT NOT NULL 
        CONSTRAINT [FK_company_Tasks_TypeId] FOREIGN KEY 
        REFERENCES [dbo].[company_TaskTypes] ([TypeId]), 
    [TaskName] NVARCHAR(50) NOT NULL
)

Note the use of the words ON DELETE CASCADE NOT FOR REPLICATION in the previous snippet.  Now, when the data record that the other table has is deleted, the child record in this table will be deleted too.  This will keep your database clean, preventing any orphaned data from being created.

In order for this to be truly effective, you need to make sure you plan your data structure ahead of time.  It is easier to get this right from the beginning, than to fix it after a release has been made where the module is in production use.

Also, you cannot just place this everywhere.  The main reason is that you will end up creating a circular dependency.  If you are successful in creating such a dependency, SQL Server will be put into a loop.  Imagine the havoc that will raise on a mission critical application.

Here is the full snippet for my table example with the DNN tokens added:

IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}company_TaskTypes_ModuleId' 
    AND TABLE_NAME = N'{objectQualifier}company_TaskTypes')
    ALTER TABLE {databaseOwner}[{objectQualifier}company_TaskTypes] DROP CONSTRAINT [FK_{objectQualifier}company_TaskTypes_ModuleId] 
 
IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}company_Tasks_ModuleId' 
    AND TABLE_NAME = N'{objectQualifier}company_Tasks')
    ALTER TABLE {databaseOwner}[{objectQualifier}company_Tasks] DROP CONSTRAINT [FK_{objectQualifier}company_Tasks_ModuleId] 
 
IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}company_Tasks_UserId' 
    AND TABLE_NAME = N'{objectQualifier}company_Tasks')
    ALTER TABLE {databaseOwner}[{objectQualifier}company_Tasks] DROP CONSTRAINT [FK_{objectQualifier}company_Tasks_UserId] 
 
IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}company_Tasks_TypeId' 
    AND TABLE_NAME = N'{objectQualifier}company_Tasks')
    ALTER TABLE {databaseOwner}[{objectQualifier}company_Tasks] DROP CONSTRAINT [FK_{objectQualifier}company_Tasks_TypeId] 
 
IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}company_TaskComments_TaskId' 
    AND TABLE_NAME = N'{objectQualifier}company_TaskComments')
    ALTER TABLE {databaseOwner}[{objectQualifier}company_TaskComments] DROP CONSTRAINT [FK_{objectQualifier}company_TaskComments_TaskId] 
 
IF EXISTS (SELECT 1 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = N'FK_{objectQualifier}company_TaskComments_UserId' 
    AND TABLE_NAME = N'{objectQualifier}company_TaskComments')
    ALTER TABLE {databaseOwner}[{objectQualifier}company_TaskComments] DROP CONSTRAINT [FK_{objectQualifier}company_TaskComments_UserId]
 
 
IF NOT OBJECT_ID('{databaseOwner}[{objectQualifier}company_TaskTypes]') IS NULL 
    DROP TABLE {databaseOwner}[{objectQualifier}company_TaskTypes]
 
CREATE TABLE {databaseOwner}[{objectQualifier}company_TaskTypes] (
    [TypeId] INT IDENTITY(1, 1) NOT NULL 
        CONSTRAINT [PK_{objectQualifier}company_TaskTypes] PRIMARY KEY CLUSTERED ON [PRIMARY], 
    [ModuleId] INT NOT NULL 
        CONSTRAINT [FK_{objectQualifier}company_TaskTypes_ModuleId] FOREIGN KEY  
        REFERENCES {databaseOwner}[Modules] ([ModuleId]) ON DELETE CASCADE NOT FOR REPLICATION, 
    [TaskName] NVARCHAR(50) NOT NULL 
)
 
IF NOT OBJECT_ID('{databaseOwner}[{objectQualifier}company_Tasks]') IS NULL
    DROP TABLE {databaseOwner}[{objectQualifier}company_Tasks]
 
CREATE TABLE {databaseOwner}[{objectQualifier}company_Tasks] (
    [TaskId] INT IDENTITY(1, 1) NOT NULL 
        CONSTRAINT [PK_{objectQualifier}company_Tasks] PRIMARY KEY CLUSTERED ON [PRIMARY], 
    [ModuleId] INT NOT NULL 
        CONSTRAINT [FK_{objectQualifier}company_Tasks_ModuleId] FOREIGN KEY  
        REFERENCES {databaseOwner}[Modules] ([ModuleId]) ON DELETE CASCADE NOT FOR REPLICATION, 
    [UserId] INT NOT NULL 
        CONSTRAINT [FK_{objectQualifier}company_Tasks_UserId] FOREIGN KEY 
        REFERENCES {databaseOwner}[Users] ([UserID]) ON DELETE CASCADE NOT FOR REPLICATION, 
    [TaskTypeId] INT NOT NULL 
        CONSTRAINT [FK_{objectQualifier}company_Tasks_TypeId] FOREIGN KEY 
        REFERENCES {databaseOwner}[{objectQualifier}company_TaskTypes] ([TypeId]), 
    [TaskName] NVARCHAR(50) NOT NULL
)
 
IF NOT OBJECT_ID('{databaseOwner}[{objectQualifier}company_TaskComments]') IS NULL
    DROP TABLE {databaseOwner}[{objectQualifier}company_TaskComments]
 
CREATE TABLE {databaseOwner}[{objectQualifier}company_TaskComments] (
    [CommentId] INT IDENTITY(1, 1) NOT NULL 
        CONSTRAINT [PK_{objectQualifier}company_TaskComments] PRIMARY KEY CLUSTERED ON [PRIMARY], 
    [TaskId] INT NOT NULL 
        CONSTRAINT [FK_{objectQualifier}company_TaskComments_TaskId] FOREIGN KEY 
        REFERENCES {databaseOwner}[{objectQualifier}company_Tasks] ([TaskId]) ON DELETE CASCADE NOT FOR REPLICATION, 
    [Comment] NVARCHAR(2000) NOT NULL, 
    [UserId] INT NULL 
        CONSTRAINT [FK_{objectQualifier}company_TaskComments_UserId] FOREIGN KEY 
        REFERENCES {databaseOwner}[Users] ([UserID]) 
)


blog comments powered by Disqus