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

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

Enforcing an Order to Validation

Cross-posted to 2 groups.

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

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

csiph-web