Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Leveraging Constraint Evaluation Sequence in SQL Server


Leveraging Constraint Evaluation Sequence in SQL Server

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1823 Visits: 2126
Comments posted to this topic are about the item Leveraging Constraint Evaluation Sequence in SQL Server

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
gand 29575
gand 29575
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 406
Thanks for an interesting helpful article.
raywatson
raywatson
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
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
h.tobisch
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 248
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
Bill Talada
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1806
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
Bill Talada
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 1806
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
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2254
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);

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search