Leveraging Constraint Evaluation Sequence in SQL Server

  • Comments posted to this topic are about the item Leveraging Constraint Evaluation Sequence in SQL Server

    Thanks & Regards,
    Nakul Vachhrajani.

    Follow me on
    Twitter: @sqltwins

  • Thanks for an interesting helpful article.

  • This article touched on an important policy regarding logical planning for database design.

    However, I am not really happy with 'generated rules' though for database systems.

    This is because things like validation are not cast in stone and often subject to change. Where possible it is better to validate based on live tables of rules, using a flexible system rather than create static table constraints.

    For example - a non blank user name constraint is a simplistic example in real life

    A user name generally must be

    1) Unique to a system ( not currently in use by another user )

    2) Not containing extended characters or expletives

    3) Minimum and maximum lengths, Not blank

    etc etc

    To create a constraint this complex, you are better off to to have a UDF or Proc, that looks up rules from tables, and provides a message about the problem.

    I remember an example where a forum had to cull out the use of certain words in usernames that were insulting in another language. By having a table of 'illegal' words, the users could readily restrict usernames without having to have the DBA recreate a fixed constraint.

    Flexibility and adaptability, combined with low maintenance design is important to time poor administrators and support staff.

  • What good is it to know the evaluation sequence of constraints ?

    - may be with the single exception of default constraints, in which special case microsoft would be well commanded to execute them first.

  • Thanks for doing so much work figuring out the order of constraints. I looked last week and could not find Microsoft documentation on it.

    The problem is bigger. Triggers play into this mess. I have "Instead Of" and "After" triggers and trigger ordering on a single table in many cases. This firing order mess is also mostly undocumented. The "Instead Of" will fire the "After" triggers if you do a DML statement for that table inside of the "Instead Of" trigger. But, my questions ran deeper, wondering if some constraints ran before or after triggers. What rows are in "inserted" and "deleted" for each trigger?

  • Two more questions I've had for some time.

    Are table and column check constraints actually different or is it just a semantic difference? And do they fire differently?

    Do FOREIGN KEYS fire in any particular order? I use CASCADE DELETEs extensively.

  • Nakul,

    Nice article. One small problem I noticed was that the constraint for being 18 years old was given as:

    (DATEDIFF(YEAR,BirthDate,GETDATE()) >= 18);

    That won't give the correct answer when the birthdate is later in the year than the current date. Wouldn't it be better to use:

    (DATEADD(YEAR, -18,GETDATE()) >= BirthDate);

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Bill Talada (1/27/2014)

    Thanks for doing so much work figuring out the order of constraints. I looked last week and could not find Microsoft documentation on it.

    Unfortunately, since the evaluation order as presented is not documented, Microsoft has no obligation to maintain it. Worse yet, there is no guarantee that the evaluation order is a a "real" order at all. It may be just what the optimizer picked for the toy example.

    As it is, I do not feel that we can confidently rely on its conclusions.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply