Enforcing an Order to Validation

[Gene Wirchenko]

Dear SQLers:

Another shot at validation, this time for real, I hope.

Suppose I have a stored procedure to handle insertions and
updates to a database table. While my front-end will have checked the
data somewhat but non-authoritatively, I want the database to check
it, too, as the authoritative validator. I see three types of
validation: primary key, foreign key, and non-key data. These are in
decreasing order of importance.

Is there any way to force the order of the checking? For
example, on an insertion, if the PK already exists, that error is more
important than that a Postal Code is in the wrong format.

One possibility is to not have any constraints for non-key
columns but instead implement those checks in the stored procedure. I
suppose that I can do something like:
insert the row
if insertion fails
get PK/FK error details
return PK/FK error details
done
if insertion succeeds
non-key error details=validate non-key columns
if non-key error
rollback
return non-key error details
done
commit
return no error
done
("done" means execution of this code stops.) This still does not
guarantee that PK checking is done before FK checking.

1) I would prefer to have the non-key column validation defined as a
constraint on the table so that it does not get forgotten.

2) Order is important. Is there a documented order or a way to set
it?

What do you do to handle validation?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
Suppose I have a stored procedure to handle insertions and
updates to a database table. While my front-end will have checked the
data somewhat but non-authoritatively, I want the database to check
it, too, as the authoritative validator. I see three types of
validation: primary key, foreign key, and non-key data. These are in
decreasing order of importance.
Is there any way to force the order of the checking? For
example, on an insertion, if the PK already exists, that error is more
important than that a Postal Code is in the wrong format.

You have a couple of devices to your disposal:

o Stored procedures.
o INSTEAD OF triggers.
o Constraints, including unique indexes.
o AFTER triggers.

Checking is roughly performed in this order. That is, you typically
first check in your stored procedure before you try to do the operation, and
then then operation consists of these steps. But in theory you could
perform post-statements checks in your procedure.

In practice, order is not that important. At least not in most cases.
After all, the basic assumption is that errors should not occur, why
errors are exceptional things.

Of course, that depends on the type of operation. For instance the
first time we have written a stored procedure, errors are very much
expected (at least when I write them). It would be very tedious if
SQL Server would give up on the first error encountered, but we do
get a bunch of errors. (Then again, you don't get the full list of
errors either.)

If you are getting data from an external source where you expect that
1-2% of the data will be incorrect, you have a case. But having a written
a lot of error-validation code, I have in many cases let it suffice
with stopping at the first error.

Now, as long as you write the code yourself, you have control and
can decide in which order to make the checks. Ehum, with one qualification:
if you have multiple triggers, you can only controll which is the first
and the last trigger.

On the other hand, there is no way to control in which order constraints
are fired. Nevertheless, you should use constraints as far as possible,
because they are so much easier to code than any other checks.

And in practice, you have no reason to be worried. I have not tested
carefully, but I'm fairly confident that all index uniqueness is
verified before FK relations. (You can easily see this in the query
plan). Where the CHECK constraints are checked, I'm less sure of.
But you could easily test.

Lastly, the main purpose of constraints and triggers is protect
the database against the application. The application should never
permit a user to enter a duplicate or an FK violation.

[Gene Wirchenko]

On Thu, 10 May 2012 23:47:32 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
Suppose I have a stored procedure to handle insertions and
updates to a database table. While my front-end will have checked the
data somewhat but non-authoritatively, I want the database to check
it, too, as the authoritative validator. I see three types of
validation: primary key, foreign key, and non-key data. These are in
decreasing order of importance.
Is there any way to force the order of the checking? For
example, on an insertion, if the PK already exists, that error is more
important than that a Postal Code is in the wrong format.

o Stored procedures.
o INSTEAD OF triggers.
o Constraints, including unique indexes.
o AFTER triggers.
Checking is roughly performed in this order. That is, you typically
first check in your stored procedure before you try to do the operation, and
then then operation consists of these steps. But in theory you could
perform post-statements checks in your procedure.
In practice, order is not that important. At least not in most cases.
After all, the basic assumption is that errors should not occur, why
errors are exceptional things.

One of the issues that I have with database errors is the
disconnect between them and the frontend. When I do error checking on
the front-end, I am careful check in a logical order. Normally, this
means that an earlier field is checked before a later one. It would
be silly to check in a random order.

I want the database errors to come out in a similar order so that
they make sense in terms of the front-end. If I have this, I can also
set the focus to the erroneous corresponding field.

Post by Erland Sommarskog
Of course, that depends on the type of operation. For instance the
first time we have written a stored procedure, errors are very much
expected (at least when I write them). It would be very tedious if
SQL Server would give up on the first error encountered, but we do
get a bunch of errors. (Then again, you don't get the full list of
errors either.)

It might be exactly what is wanted.

Post by Erland Sommarskog
If you are getting data from an external source where you expect that
1-2% of the data will be incorrect, you have a case. But having a written
a lot of error-validation code, I have in many cases let it suffice
with stopping at the first error.

It will be from user input thus error-prone.

Post by Erland Sommarskog
Now, as long as you write the code yourself, you have control and
if you have multiple triggers, you can only controll which is the first
and the last trigger.
On the other hand, there is no way to control in which order constraints
are fired. Nevertheless, you should use constraints as far as possible,
because they are so much easier to code than any other checks.
And in practice, you have no reason to be worried. I have not tested
carefully, but I'm fairly confident that all index uniqueness is
verified before FK relations. (You can easily see this in the query
plan). Where the CHECK constraints are checked, I'm less sure of.
But you could easily test.

I am trying to find out what is the guaranteed behaviour. That
is safer.

Post by Erland Sommarskog
Lastly, the main purpose of constraints and triggers is protect
the database against the application. The application should never
permit a user to enter a duplicate or an FK violation.

I think that statement should be that the database should never
permit such, but the application should be free to do no error
checking. The application also doing error checking makes for a more
friendly app, but I would prefer correctness FIRST.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
One of the issues that I have with database errors is the
disconnect between them and the frontend.

Yes, down in the database, you cannot really tell what error the user did.
All you can tell is that a constraint etc was violated.

Particularly, if you want error message that helps the user, constraints
are not your guy.

Post by Gene Wirchenko
I am trying to find out what is the guaranteed behaviour. That
is safer.

I gave it some more thinking, and there is hardly any guaranteed order.
There is an optimizer and it makes the actions in the order which is the
most efficient.

Post by Gene Wirchenko
I think that statement should be that the database should never
permit such, but the application should be free to do no error
checking. The application also doing error checking makes for a more
friendly app, but I would prefer correctness FIRST.

Let me make it clear again: the error messages that are produced by
SQL Server when a constraint is violated are not helpful for the user.
At best you can trap and interpret them. But that is difficult.

And since the error messages are useless to the user, it's completely
irrelevant in which order SQL Server checks the constraints.

Error messages from stored procedures and trigger checks are a different
matter, here you have control. Then again, down in the trigger all
you can tell that a column has an illegal value, but you don't know
where in the form the user went wrong. You don't even know if there is
a form.

Finally, permit me to point out that the application can implement error
checking more than one way. If a column only permits the values A, B and C
the application will of course not expose a text box, but a drop-down or a
ratio button.