Vishal Marya
hooked on development
   
Recent Posts
Home
Development
Work
Certification
MCP
Work Profile
Presently
Experience
For recruiters
Meenal
Space
Vishal Marya
Email
About
is working on Microsoft .Net  and
SQL Server  since 2007. Earlier
developed numerous applications
is Visual Basic, Crystal reports, Com,
Activex and MSAccess.
Current Job Profile
Curriculum Vitae
PDF DOC XPS MHT
 
 
$1.99/Mo. for 12 months of Economy Hosting at GoDaddy.com
How important are database constraints  
 
by Vishal Marya | Nov 24, 2010
 
TAGS  
Be the first to comment.
 
How important are Database Const

 

 

Like a software application, a good database is built with a lot of planning and forethought based on the data entities and relationships its going to accommodate. RDBMS like SQL server, MYSQL, Oracle, PostgreSQL etc provide various features to protect data integrity. These features are implemented as constraints which are stored as scheme objects in the database itself.

 

The constraints like primary key, foreign key, Unique, check, Not Null  etc  prevent the users or applications from entering inconsistent data and ensure that data within our database is always "correct". This is done by placing the fundamental business rules within the database.

 

This vital aspect of any database application is at times leniently handled by the software developers. Developers tend to code for the business and data integrity rules inside their applications ignoring the very important role of the database constraints.

 

In this article I would state some reasons why I think database constraints are necessary and provide references from the problems I had faced while working on the applications which didn't employed database constraints.

 

 

Data Validation is close to Data

 

All the database constraints mentioned above can be implemented in the business rule component of any application. But placing the data validation rule in the database itself saves one round trip to the database server.

 

Example:

Imagine a scenario where an application is used to create a Quotation document which requires the user to put in a unique reference number. Now before committing the quotation data to the database, the uniqueness of the reference number has to be verified. If its done in the business rule component of the application, it connects to the database to check if the reference number exist, and if it doesn't, it again connects to the database to actually persist the quotation data in the database.

 

The same data validation check can be enforced by creating a unique Key constraint to the Quotation's reference number field in the quotation table. The application sends the update command, that passes through the unique key constraint. If it succeeds, it commits the changes. In case the data violates the constraint, exception is raised which can be handled by the application appropriately. So in this case, I made the connection to the database only once.

 

The extra round trip becomes more significant in case of an N-tier application.

 

Centrally located rules serving all

In a real world, any data would be confined to some checks and rules. The rules being part of the data definition, it has to exist with those rules and any application which is targeting that database has to adhere to those rules. So it makes complete sense to keep the constraints with the data itself. Different applications updating the data in that database need not apply those data validation checks individually.

Example:

In a N-tier web application which is serving the information needs of university students, all the business rules are implemented inside the business logic layer of the application. The university decides to outsource the Practical sessions and evaluation of computer practical examinations to small private institutes. The university's IT department decides to create a web service which will connect to the university main database and serve the data needs of the private institutes, including the upload of the student's practical grades. Because no constraints were applied at database level, constraints which were relevant to data updated by the new web service had to be duplicated.

Please note that every time there is an addition in the business rules, it has to be duplicated in the web service as well. If the constraints were applied at database level, a lot of work would have been avoided.

 

Referential integrity works better with Foreign Key constraints

Referential integrity enforced by the Foreign Key constraint is one aspect of the database design I cannot live without. Defining Foreign Key constraints within the database has a lot of advantages:

  • Methodically creates relationships between tables and help in visualizing the entity relationships in our database
  • Implements a business rule which enforces a value to be present as a primary\unique key before we can use it in another table.
  • We can prevent a primary\Unique key from getting deleted if the corresponding Foreign Key exist.
  • When the primary key record is deleted, we can set it to cascade delete the child records or  set the corresponding foreign key field to NULL or some default value.

       All this can be achieved by writing code, but that would be a lot of work as compared to creating a relation ship using some visual tool.

        

The database is always in a consistent state. Event during development process.

If database constraints are not applied along with the database tables, there is every possibility of incorrect data getting into the database especially during the development process.

During the development process, various modules of the application are tested and errors are generated. If database constraints are not applied along with the database tables the errors generated during the tests can leave the database in an inconsistent state. Such incorrect and inconsistent data can lead to more application errors. To test the modules successfully, the developers have to repeatedly take up the task of cleaning the incorrect data manually. This adversely affects the productivity of the developer.

In addition to that, many tables have to be manually populated to test some parts of the partially completed application. This manual exercise can also lead to incorrect data. These problems can be eradicated by employing the database constraints.

 

Concurrency Violation savior

In multi-user applications the database constraints become all more important. Consider a scenario where John had logged into the application to create a quotation which includes a number of products. The business rule dictates that only the products available in the product catalog table can be included in the quotation. Now if this business rule is implemented in the application itself, the application connects to the database to validate this condition for each product included in the quotation and again connects to the database to update the quotation.  Now before John's application instance issues the update command to commit changes, another user deletes one the product which was included in the quotation by John.

The John's transaction is also committed and now database contains the data which violates the business rule already implemented in the application code.

Identifying all such conflicts and coding for them in a big enterprise level application is an uphill task. Defining database constraint is a much better alternative. This particular situation can easily be handled by applying foreign key constraint on the quotation product field.

 

Conclusion

 

Implementing the data constraints and validation rules within the database is always a good option. The fundamental rules like primary key, Not NULL, unique key, foreign key etc should always be defined in the database. By doing so we make sure that from where ever the data has entered the database, it is in compliance with the underlying business rules.

 

 
Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries.