Database Constraints

November 22nd, 2008

In my last post I discussed Application Wide Data Integrity and how it was important that the business rules to ensure data integrity were captured in the database.

Using database constraints is the preferred method of defining ways in which the integrity of data in a database can automatically be enforced.  In essence, constraints define rules regarding permissible values that are allowed in columns, they define the rules that define interrelationships between tables and can check and modify the data in a database.  They provide the ability to assist in applying business logic to the application at the database level – to centralize and simplify it to make the development of database driven applications easier and more reliable.  Constraints are, in reality, restrictions on the data that can be placed at either the column or table level.

At a very high level, there are three types of constraints:

* Entity Constraints
* Domain Constraints
* Referential Integrity Constraints

Entity constraints are constraints on a row within a table.  The two types of Entity Constraints are:

* Primary Key
* Unique

Essentially, the Primary Key and Unique constraints allow you to enforce uniqueness within a table at the database level.  Examples of where this is important might be in the use of Social Security Numbers, customer id’s, transaction id’s and order id’s where it is imperative that one and only one instance of each of those exist within the database.

Domain constraints are column level constraints and could be against one or more columns.  The three types of Domain Constraints are:

* Check
* Rules
* Defaults

The incorporation of Domain constraints provides a single, central repository for ensuring that data related business rules are satisfied and helps to eliminate or reduce the creeping of data that doesn’t make logical sense into the data base.  Examples could be ensuring usernames meet a set of predetermined rules, social security numbers contain 9 digits and salary values are non-negative.

Referential Integrity constraints are used to enforce that the values in one column must match the values in another column, either in the same or a different table.  There is one type of Referential Integrity Constraint:

* Referential Integrity

The use of Referential Integrity constraints ensures that relationships between tables remain consistent.  Examples of circumstances you are trying to prevent include deleting customer records when and order record for that customer exists in another table or deleting an order record when products related to that order exist.

Again, all of these rules can, and should, also be enforced at the application level.  However, to avoid potential data related problems when developers forget, get lazy or just don’t know the data integrity rules, it is a best practice to identify and spend the time developing the rules when the database is designed.  Taking the time early in the development of the application will more than pay for itself later on.