More Foreign keys? - SQL and SQL Server

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

Home All Forums

5/3/2011 6:40 PM

Robert Folkerts

Joined: 5/3/2011

Posts: 3

More Foreign keys?

I was looking through the tables and there are several cases where there are columns that look like they could use a foreign key to improve data integrity. For example, I found six tables that have a ModuleID, but do not have a foreign key that references the Modules table. To find these systematically, you can execute:

select t.name, c.name, fk.name
from sys.columns c
join sys.tables t on c.object_id = t.object_id
left join sys.foreign_keys fk on t.object_id = fk.parent_object_id
left join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
where c.name = 'ModuleID' and fk.name is null
order by t.name;

There are some other column names that suggest that they should be foreign keys:

Should columns named CreatedByUserID or LastModifiedByUserID reference the column Users.UserId?

Should Permissions.ModuleDefID reference ModuleDefinitions.ModuleDefID?

Should columns named PortalID have a foreign key referencing Portals.PortalID? (Except, of course that Portals.PortalID should not reference itself)

5/3/2011 8:21 PM

Wes Tatters

Joined: 4/30/2004

Posts: 2155

Re: More Foreign keys?

The danger with foreign keys is that they actually reduce system performance. Requiring the SQL server to make additional lookup validations during INSERT and UPDATE calls on multiple tables.

In DNN terms the question comes down to - does cost of this extra performance hit - bring any significant benefits. Given that the core business rules should already be managing data integrity.

Westa

5/4/2011 7:55 AM

Sebastian Leupold

www.dnnwerk.de
Joined: 3/8/2004

Posts: 46212

Re: More Foreign keys?

Robert,

please keep in mind that DotNetNuke is a modular system. consisting of the core framework and numerous extensions (modules, providers, skin objects, skins and others). Though the database should take care of data integrity, this bears the problem, how to avoid dependencies limiting the options for core changes. Unfortunately, modules may only place a foreign key on the modules table (those missing it might use a trigger or code to remove obsolete data - the best option would be a ModuleDeleted event sent to the module to avoid ANY data coupling.

Same applies for auditing columns in module tables.

For auditing in the core, there is a problem with new feature "hard delete user". If the user record is deleted, how to update all records referring to it? Setting it to Null would remove any option to retrace later, who created or edited a record. In my opinion, there should be no hard delete of user records, instead sensitive data should be removed from the user record and record needs to be kept, but unfortunately this is handled differently in the core.

From my experience, the slight performance decrease by foreign keys is not significant, as long as they are properly indexed (which is usually needed anyways)- they are heavily overcompensated by effort needed to ensure data integrity from business layer.

Cheers from Germany,
Sebastian Leupold

Speed up your DNN Websites with TurboDNN

5/4/2011 9:46 AM

Robert Folkerts

Joined: 5/3/2011

Posts: 3

Re: More Foreign keys?

Thank you for the thoughtful replies. My experience has been that in any enterprise system, users will find ways around application-based data integrity checks. So I prefer explicit foreign keys for roughly the same reason that I prefer seat belts in cars - you only need them when things go wrong and no matter how careful you are, there is always somebody else involved.

I am looking at DNN as part of an enterprise-wide solution, so the users in our system are also business partners and paying customers. So at least in our case,changes to the users are something that we wish to apply thorough integrity constraints. So I share your perspective on the 'hard delete users' issue. If there are compelling reasons to allow users to be hard deleted, the foreign keys can be defined with an 'on delete set null' option. This will allow uses to be deleted.

But, any time that data is lost, it represents an irreversible transition. That just thermodynamics and it completely supports your view on the 'hard delete' issue. Adding some sort of 'active flag' on the user record is a reversible solution.

Given that I want to enforce these integrity constraints, can you forsee any specific issues with adding these foreign keys? If these keys are added, they will prevent a referenced User record from being deleted. But, I don't know DNN well enough to anticipate other side effects that may result if I go and add keys. I know that this can become an issue if there are cases where the application code violates the foreign keys 'temporarily' during a transaction (e.g. if you have a transaction that replaces a user with a new user and sets all of the LastModifiedByUseId values that referenced the old user to the new user, it would be easy to write the transaction in a way the temporarily violates the foreign key.)

I also recognize that the modular nature of DNN complicates the enforcement of foreign keys. Still, I think that any module that references tables from core should be expected to add complete foreign keys. So added tables that reference Modules and Portals should be using a foreign key constraint, at least in my opinion.

Thank you again for your thoughtful reply to to a forum newbie.

5/4/2011 7:28 PM

Wes Tatters

Joined: 4/30/2004

Posts: 2155

Re: More Foreign keys?

One of the key risks you will face is the potential of breaking future upgrade paths. The dnn core upgrade system works based on assumptions about the state of the database system.

There is literally nothing stopping you adding additional foreign key references or indexes to you dnn install - but you would need to understand that it is possible that these may conflict with future updates. Though that may be as simple to resolve as running a script to remove the foreign keys you added before running any upgrade - and then re-adding the keys after you finish the upgrade.

With regard to the CreateByUserID and LastModifiedByUserID columns - I would suggest that you maybe dont constraint these - ultimately they are reporting and tracking fields that have little of no structural benefit - and given that it is now possible to HARD DELETE users as well as SOFT DELETE users constraining those fields would make a HARD DELETE impossible in any circumstance where a user has made any change to the system that triggered a LastModifiedByUserID column.

The other complication you would need to evaluate is that the core business rules which managed access to the sql tables through the sql dataprovider and stored procedures currently may not have any error management code in place to deal with errors created by your new foreign keys.
As such triggers fired on these new constraints would most likely degrade down to a system lvl error.

Though i guess the question would still remain - what are you trying to achieve in doing this. Unless a user has HOST level access to the system then they should have no direct access mechanism to the sql core data structures.

Westa

Page 1 of 2

These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
No Flaming or Trolling.
No Profanity, Racism, or Prejudice.
Site Moderators have the final word on approving / removing a thread or post or comment.
English language posting only, please.

Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

DNN Corp. (DotNetNuke)