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

Posted on February 22, 2011, in Database Design, Performance Tuning, Sql Server 2005, Sql Server 2008. Bookmark the permalink. 1 Comment.

  1. >and what kind of database was it ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: