Leveraging Constraint Evaluation Sequence in SQL Server

  • Nakul Vachhrajani


    Points: 10219

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

    Thanks & Regards,
    Nakul Vachhrajani.
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • gand 29575

    Mr or Mrs. 500

    Points: 548

    Thanks for an interesting helpful article.

  • raywatson

    SSC Journeyman

    Points: 75

    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.

  • h.tobisch


    Points: 1671

    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.

  • Bill Talada


    Points: 11956

    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?

  • Bill Talada


    Points: 11956

    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.

  • Thomas Abraham


    Points: 10761


    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

  • gbritton1


    Points: 6520

    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 8 (of 8 total)

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