SQL Server: Why We Should Prefer Database to Implement Data Integrity

Recently, I had explored a client database, in which not a single domain or referential constraint was used to implement data integrity. And such integrities are implemented through code on application side.

At database side following domain and referential constraints can be used to implement data integrity.

• FOREIGN KEY
• CHECK
• DEFAULT
• NOT NULL

Out of these four constraints, DEFAULT has less importance as compare to other three. Developers mostly like to implement domain integrity constraints through code on application side, and like to avoid implementing these through CHECK constraints, on database side. But one should keep in mind that implementing such business rules in the database using CHECK constraints are always helpful for optimizer to generate efficient execution plans.

For remaining two, one should totally depend on database and these (FOREIGN KEY and NOT NULL) constraints should be implemented on database side. Because, through database you are not only using domain and referential constraints to implement data integrity but also TO FACILITATE THE OPTIMIZER TO GENERATE EFFICIENT QUERY PLAN FOR YOU.