Introduction to Constraints

Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should only be one row for each product number.


To that end, SQL allows you to define constraints on columns and tables. Constraints give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.


A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.


The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database.

The following categories of the data integrity exist:

• Entity Integrity
• Domain Integrity
• Referential integrity
• User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows in a table. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table.

Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital) i.e., Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint. The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, you cannot remove a row from the Products table if an order detail references the ProductID of the row. Note: Entity and referential integrity together form key integrity.

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values. Domain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints. These are the constraints we will cover in this article, but be aware there are other options available to enforce domain integrity. Even the selection of the data type for a column enforces domain integrity to some extent. For instance, the selection of datetime for a column data type is more restrictive than a free format varchar field.

The following list gives a sampling of domain integrity constraints.

• A product name cannot be NULL.
• A product name must be unique.
• The date of an order must not be in the future.
• The product quantity in an order must be greater than zero.

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.