What? All of the constraints are disabled? (T-SQL Tuesday #88)

T-SQL Tuesday is a monthly event where SQL Server bloggers write a post based on a subject chosen by the month’s host. And although this is only the second actual post of this blog (fret ye not, there’s more on the way), I’m delighted to finally be participating more in the SQL community that has helped me tremendously over the years.

This month’s #tsql2sday event is being hosted by Kennie Nybo Pontoppidan (b|t) and this months topic is: The daily (database-related) WTF.

The topic couldn’t be more timely for me, actually.  My first post detailed the beginning of a long journey into overhauling our 18-ish year old DB schema and architecture for our flagship application.  Now undergoing its third major revision, the EnergyCAP database has served us well for many years and thousands of clients… but the time is right for digging in and pulling this classic into the age of modern SQL Server.  I have a queue of blog posts about how we’ve gone about this work and how we’re solving problems anew (for us at least), but one discovery during the process still causes me to shake my head.

So sit right back and you’ll hear a tale… (a tale of a fateful trip… sorry, couldn’t resist)

The lay of the land

Our database isn’t huge by most accounts, but at 331 tables in a standard database, it’s not small either.  There was significant funding behind the original design back in the good old Enron days (yes, that Enron…) and so they made plenty of good design choices that still serve us well today.

As you’d expect, one of those choices was being diligent about foreign key setup and maintenance, largely facilitated by a data modeling tool.  At the time they chose ErWin.  All of these years later, I can’t tell you exactly why they chose it or if there were any other options considered.  I don’t even know anyone on the team from 1999 that went about taking a solid database design and bringing it to life with ErWin, modeling the design documents in the application and then back out into creation and migration scripts.  Most of it was executed well.

Except for that one thing that never set well with me… that one thing that I never really spent time digging into. (Thank you, Mr. Hindsight…)

Although we had Foreign Key constraints neatly setup and maintained in the schema, helping us to draw beautiful ER diagrams, all of our data integrity logic was held in triggers.  I also never thought to ask why all of our FKs had names like “r_0013”.

The WTF moment

And so imagine my surprise a few months ago, having finally freed up some time to seriously dig into our DB, when I came across one of our typical migration scripts trying to understand some recent changes.  I noticed something I’d never seen before in a part of the script that was trying to modify a Primary Key for some testing we were doing.  First we had to drop some Foreign Keys so that we could modify the Primary Key indexes.  But then I saw this as the FKs were being added back in:

[sql]ALTER TABLE [dbo].[abc] WITH NOCHECK ADD CONSTRAINT…[/sql]

Huh?  “WITH NOCHECK”?  Um… has that always been there?  Was this just a fluke because these were one-off testing scripts?  Surely we wouldn’t disable all of our constraints, right?  I mean, besides data integrity there are lots of potential small Query Engine wins wrapped up in all of those constraints.

Still looking at the screen somewhat baffled, I picked up my phone and dialed the extension of our Developer/part-time DBA.

“Afternoon Joe! Hey, I have another one of those ‘out of left field’ questions for you.”  (I pause to give him time to roll his eyes.)

“I’m looking at these testing scripts and I noticed that we were adding back all of the Foreign Keys with NOCHECK.  Have we always been doing that or is this just some stuff in this script?”

After a slight pause Joe responds, “Um, well, yeah…” It’s apparent that he’s quickly getting the gist of my question.  I’m also sure he’s wondering why I’m asking this now after being around this joint for years.  “All of our FK constraints have always been created with NOCHECK.  I guess it was just an ‘old school’ way of doing the DB design back in the day… relying on triggers for the data integrity and better, human readable constraint failure messages.  We’ve just never changed things over all these years.”

Wow.  721 Foreign Key constraints and not one of them is enabled.  The Query Engine can’t rely on one of them to help it make a decision.  We can’t be assured that any of the related data is 100% reliable.

“OH!” I say, realizing why those triggers have never really sat well with me.  “In all of these years, Joe, I honestly never made that connection… why we have all of the data integrity logic in triggers.  Let me ask you something else.  What about the names like ‘r_0013’ or ‘IF345TableABC’.  I’ve never asked what the significance of the numbering scheme was for FKs and Indexes?”

“Well, there isn’t any.  Those are just auto-generated from the default templates that came with ErWin.” (crickets…)

“Really?  All of these years I assumed there was some secret code or standard that helped us determine those names… and it’s just defaults? Man, I’m going to have to digest this one for a bit.  Thanks for humoring me… as always.”

But wait, it gets better (or worse)!

Perspective really matters when a discovery like this is being made.  At the same time I was working through all of this we were spinning up a new project and one engineer downloaded the newest copy of ErWin to try.  He quickly created a model for the DB we needed to get the project rolling and within a few hours created the initial scripts.  To be honest, I didn’t examine the scripts closely at the time.

This past Friday however, I had to dig into the database to fix a design problem and so I opened up the creation script that was generated from ErWin.  Mind you, the developer hadn’t changed any templates or default settings.  Sure enough, I saw a similar pattern to how ErWin functions with a version that’s nearly 20 years old.

• Indexes have (nearly) useless names
• Foreign key constraints are named with an incrementing number rather than any helpful information AND the cascading logic is missing
• All true constraint logic is done in triggers

At least the FK constraints aren’t disabled! 🙂

Where do we go from here?

This “new” revelation is just one in a line of DB updates we’re making and testing right now.  While re-enabling the constraints and making sure all business logic persists as expected (sans triggers) isn’t trivial, it’s now near the top of our list.  There is some trigger logic that is legitimate so we have to figure out how to carefully unravel this puzzle.

Ultimately we hope to better utilize the strengths of the SQL Server Query Engine by working with it.  We also hope to clean up some issues with referential integrity that have gone undetected because triggers were sometimes disabled for big data loads leaving bad data behind.

But more than anything, it’s caused us to talk more openly about the tools we use, how far along are we in “mastering” those tools, and if there is a way to better measure “good” design from “bad” design.

And of course, check any program defaults. Sigh.