How important are Database Const
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.
constraints like primary key, foreign key, Unique, check, Not Null
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
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
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.
Validation is close to Data
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.
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
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.
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
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
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
- 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.
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
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
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
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.
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