SQL Server Coding Standards: Best practices with Constraints

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Data integrity is an important aspect of any database. Data integrity is implemented by using constraints.

Use constraints wisely as per the requirement and nature of the application. Constraints enforce data integrity rules but using lot of constraints may realise overhead when performing DML operations on data.

So, use them as per the requirement of the application. Generally, you should use constraints to enforce the data integrity. Following are some of the guidelines which should be followed:

Creating a clustered primary key constraint is generally the best practice. You should have one primary key constraint preferably clustered on every table in your database.

To maintain data integrity, use unique constraints when a column is not expected to hold duplicate values in any case. For example, a unique constraint should be defined on Social Security Number.

Apply referential integrity with foreign key constraints whenever possible to establish relationships between primary table and foreign table to maintain data integrity.

Always, try to apply NOT NULL constraints on columns wherever possible and keep minimum columns nullable as possible. This disallows columns to have null values and simplifies the queries and other DML operations.

When it is required for a column to allow NULL values, try to define a default value by creating a default constraint on that column.

When a column is expected to hold a specific domain of values of either a set of predefined values or a range of values, then apply check constraints to maintain the data integrity on such columns.