locked
check constraint in database design V.S validation in application RRS feed

  • Question

  • why we use check constraints in database design whereas we can use validation in application with more power and  faster and without sql server overload?

     

    Saturday, February 4, 2012 8:35 PM

Answers

  • Hi Iman,

    Constraints in the application would not prevent you from inserting invalid data outside of the application. Nothing would prevent you or other users to open management studio, for example, and insert invalid data there. Even if you have the rules that prevent people to do it now, things tend to change. Organization workflow and processes could be adjusted at the future, new applications can be developed, you could have the bugs that skip the checks, etc. Constraints in the database protect you from those things.

    in addition to that, if you enforce unique constraints in the application, you should keep multiuser environment in mind. Its almost impossible to check uniqueness in the application without compromising in concurrency. 

    last but not least, sometimes its easier to change the constraint in database rather than recompile and redeploy the application 


    Thank you!

    My blog: http://aboutsqlserver.com

    Sunday, February 5, 2012 4:20 AM

All replies

  • Hi Iman,

    Constraints in the application would not prevent you from inserting invalid data outside of the application. Nothing would prevent you or other users to open management studio, for example, and insert invalid data there. Even if you have the rules that prevent people to do it now, things tend to change. Organization workflow and processes could be adjusted at the future, new applications can be developed, you could have the bugs that skip the checks, etc. Constraints in the database protect you from those things.

    in addition to that, if you enforce unique constraints in the application, you should keep multiuser environment in mind. Its almost impossible to check uniqueness in the application without compromising in concurrency. 

    last but not least, sometimes its easier to change the constraint in database rather than recompile and redeploy the application 


    Thank you!

    My blog: http://aboutsqlserver.com

    Sunday, February 5, 2012 4:20 AM
    1. A database is for the data. If the data is to be valid, the database needs to constrain it.
    2. In most cases, i doubt an application is faster than the database.
    Monday, February 6, 2012 2:01 PM
    Answerer