Leveraging Constraint Evaluation Sequence in SQL Server

Question

Post reply

Leveraging Constraint Evaluation Sequence in SQL Server

Nakul Vachhrajani

SSChampion

Points: 10294
More actions
January 26, 2014 at 11:45 pm

#287731

Comments posted to this topic are about the item Leveraging Constraint Evaluation Sequence in SQL Server
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

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

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

gand 29575 SSChasing Mays Points: 618 More actions · Answer 1

gand 29575

SSChasing Mays

Points: 618

January 27, 2014 at 12:31 am

#1683431

Thanks for an interesting helpful article.

raywatson SSC Journeyman Points: 75 More actions · Answer 2

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 SSCommitted Points: 1671 More actions · Answer 3

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 SSChampion Points: 11954 More actions · Answer 4

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 SSChampion Points: 11954 More actions · Answer 5

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 SSChampion Points: 10761 More actions · Answer 6

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 7

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.