Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1036 > unrolled thread

Enforcing an Order to Validation

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-10 12:42 -0700
    Re: Enforcing an Order to Validation Erland Sommarskog <esquel@sommarskog.se> - 2012-05-10 23:47 +0200
      Re: Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-10 15:05 -0700
        Re: Enforcing an Order to Validation Erland Sommarskog <esquel@sommarskog.se> - 2012-05-11 09:01 +0200
          Re: Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-11 09:50 -0700
            Re: Enforcing an Order to Validation Erland Sommarskog <esquel@sommarskog.se> - 2012-05-11 22:29 +0200
              Re: Enforcing an Order to Validation Gene Wirchenko <genew@ocis.net> - 2012-05-11 22:10 -0700

#1036 — Enforcing an Order to Validation

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

[toc] | [next] | [standalone]

#1037

Gene Wirchenko (genew@ocis.net) writes:
>      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. 


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1040

On Thu, 10 May 2012 23:47:32 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      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.

     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.

>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.

>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.

>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.

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

>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

[toc] | [prev] | [next] | [standalone]

#1042

Gene Wirchenko (genew@ocis.net) writes:
>      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.

>      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.
 
>      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.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1047

On Fri, 11 May 2012 09:01:22 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      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.

     I know.  I want to connect the two.  I wish it were easier.

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

     I am fine with having to convert.

>>      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.

     I expected that would be the case, but I decided to ask, just in
case.

>>      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.

     I do not see why, at least, for my use.  Suppose a Postal Code is
invalid.  That column's constraint fails.  If I send back tothe
front-end that Postal Code has an error, I simply issue an error
message that the Postal Code is invalid.

     What difficulty are you thinking of?

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

     The order of the *errors* -- not error messages -- is important.

>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.

     I will inmy design.

>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.
 ^^^^^
     "radio".

     No, not "of course".  For data entry speed, the current app down
not use dropdowns, and I expect this to continue with the Web
front-end version.

     The front-end can issue whatever error message is appropriate to
it once it knows that there was an error on a given column/field.


     Thank you for your time and comments.  Aside from alternatives
suggested, this helps me make sure that I really want things the way I
want them.

Sincerely,

Gene Wirchenko
    

[toc] | [prev] | [next] | [standalone]

#1048

Gene Wirchenko (genew@ocis.net) writes:
>      I do not see why, at least, for my use.  Suppose a Postal Code is
> invalid.  That column's constraint fails.  If I send back tothe
> front-end that Postal Code has an error, I simply issue an error
> message that the Postal Code is invalid.
> 
>      What difficulty are you thinking of?
 
If you rely on the CHECK constraint, you will at some point you will have to 
parse the error message that SQL Server produces when the constraint is 
violated. That is doable, but I am not sure that I recommend it. It does
happen that Microsoft change error messages between releases, which could 
break your code.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1049

On Fri, 11 May 2012 22:29:39 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I do not see why, at least, for my use.  Suppose a Postal Code is
>> invalid.  That column's constraint fails.  If I send back tothe
>> front-end that Postal Code has an error, I simply issue an error
>> message that the Postal Code is invalid.
>> 
>>      What difficulty are you thinking of?
> 
>If you rely on the CHECK constraint, you will at some point you will have to 
>parse the error message that SQL Server produces when the constraint is 
>violated. That is doable, but I am not sure that I recommend it. It does
>happen that Microsoft change error messages between releases, which could 
>break your code.

     Ah.  I was hoping that there was a function that would return the
name of the constraint that failed.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web