Hitting the upper limit of foreign key constraints

There are bugs, and then there are bugs.  We recently hit one that fell directly in crazy-town category.  What exactly would you do if you get this fun message:

The query processor ran out of stack space during query optimization. Please simplify the query.

We got this from a DELETE query without a where clause.  Thinking it might be an NHibernate issue, NHProf didn’t show any issues.  Looking at the stack trace, that’s where we saw that this exception came from System.Data.SqlClient classes, not NHibernate.  Popping over to SSMS, doing the same query there got the same exception.

For a while, it looked like DELETE was broken.  But looking closer, we saw something interesting about this specific query.  It was only one table in our system that would get this message, and it was our root User table.  It’s nothing exciting, looks something like this:

image

But the more interesting piece came from a requirement that every entity keep track of:

  • Create date
  • Creator (User)
  • Update date
  • Updater (User)

It didn’t matter about history, just who updated it last and when.  We used this information on a little widget on each of our entity view screens (for those entities that had a simple view screen).  But the issue came in how we modeled this relationship: directly on each entity.  Supposing we had a product entity, the resulting DB schema would be:

image

We show things like the user’s full name and so on in this widget, so we thought that linking back to the original user table would work best.  It’s fairly trivial to do this in NHibernate (without resorting to things like triggers).  However, that’s two foreign key constraints per entity.  One limitation of SQL Server we learned the hard way however is that:

SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.

We went along happily for about 150 or so entities in our system, until we hit around 300 foreign keys against the LoginUser table, causing build failures on a couple of our branches in the exact same sprint.  It was rather surreal, to have separate feature branches encounter the problem at the same time.  The only piece we found in common between these branches was that they all added a new entity, breaking some threshold on all of our boxes (and the server).

So how did we fix this problem?  For now, we just run a script to blast through our entity tables to drop constraints.  In the future, we’ll likely take another design route to store the pseudo-audit information in another table.

And I really thought DELETE was broken…

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in SQL. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Nicholas Becker

    That dastardly sql server strikes again.

  • http://morten.lyhr.dk Morten Lyhr

    De-normalization too the rescue.

    Seriously!

    The argument against it would be that every table would have duplicate column names (the audit columns).

    The counter argument would be that your solution has duplicate foreign key contraints (the audit foreign keys).

    On the possitive side all your queries does 1 or 2 less joins, so they should be faster.

    On the negative you need some sort of strategy in case a user changes. Either keep the de-normalized data (historically correct), or update the 300+ tables (Something that does an sql update on an whole table, and automagically goes through all tables looking for the audit columns).

    Just my 2 cents.

  • http://ramonsmits.com Ramon Smits

    I always learned to disable foreign key contrained in a production database. I always have them enabled in development and test but not in acceptance (real-data) en production.

    FK checking becomes a real problem in large update/insert batches. Most applications have a logic tier that already does this sort of checking. Especiallly “modified by” is ridiculous to check on FK.

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @Morten

    We’re definitely looking at alternatives. Because we only show the date of the change, some smart caching could allow us to make that piece a separate DB call.

    @Ramon

    Now that is an interesting concept – I have been wary of turning them all off, but since we have 400+ automated UI tests and thousands more other automated tests, what are FKs adding in production? I wonder now…

  • http://realfiction.net Frank Quednau

    Don’t you love such bugs?
    In our current project it looks like transactions are broken sometimes (on Oracle, with deferred constraints). Many of my colleagues have strong roots in Oracle land, I think telling them to skip FK constraints will solidify my role of being a mad man.

  • Jay

    I’m curious to know how you guys went about with a root cause analysis of this bug? Did you use the 5 why’s, did you use a different methodology, or did you guys just “figure it out”?

  • Jeff Gonzalez

    @Ramon
    Really interesting. PK/FK’s are a logical database concept that are materialized through the constraints in SQL Server. They are a domain concern, for sure. I think if you can leave them off in production, this is a good sign you are doing things right.

  • Pete Nelson

    Interesting… We have the same architecture in our application at work. Our User table is at 290 FKs right now and it’s still working, but it’s good to know if it ever breaks.

  • http://www.diogomafra.com.br Diogo Edegar Mafra

    Jimmy, are you using MSSQL 2000 in a 32bits OS?

    I saw this problem some time ago, this is a limitation of this version. If I remember correctly, you must upgrade to MSSQL 2000 64bits or to 2005.

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @Jay

    It wasn’t me that figured it out, but it was Google that told us the answer :) Nothing like putting the exception message in google and seeing what pops out the other end.

    We did look for commonalities in DB changes, and saw that we both added a new DB table, so we had an idea where to start.

  • jeff_yao

    @Ramon Smits, I could not agree more with you. I did the same in my production for some foreign keys, such as one FK referring to a table with 2 billion records, and the Upd/Ins/Del becomes such an overhead that the production usablility becomes an issue. I still remember when I mentioned what I did in a forum, I was “attacked” by so many people, I just backed away and never wanted to argue with people who never experience the scenario and only argue based on theory or so-called best practice

  • Drakec

    I am using these constraints on a lookup table that populates the dropdown lists in our application. This table contains about 70,000 rows and is fairly static. If you cannot use FK/PK constraint, then how else can you ensure that the administator does not delete a child record that is referenced from a parent record?

  • Drakec

    I am using these constraints on a lookup table that populates the dropdown lists in our application. This table contains about 70,000 rows and is fairly static. If you cannot use FK/PK constraint, then how else can you ensure that the administator does not delete a child record that is referenced from a parent record?

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @Drakec

    We didn’t get rid of _all_ our constraints, just the ones for this audit information.

  • Drakec

    I am not using any of the constraints for audit information, simply to enforce referential integrity.

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @Drakec

    Right, we still kept all of the other FK constraints in our system.

  • Vladimir Sotirov

    The audit table should be
    Product
    id
    name
    updatedby
    updateddate
    updatetype
    this cuts down your FK in half.
    also for audit you can use SQL Server Audit if you are 2008.
    http://msdn.microsoft.com/en-us/library/cc280386.aspx