Constraints

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.

Types of Constraints:

There are several ways to implement constraints, but each of them falls into one of three categories—

Entity,

Domain, and

Referential integrity constraints

Entity Constraints

Entity integrity ensures each row in a table is a uniquely identifiable entity. 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.

Domain Constraints

Domain constraints deal with one or more columns.

They ensure that a particular column or set of columns meets particular criteria. When you insert or update a row, the constraint is applied without respect to any other row in the table; it’s the column’s data you’re interested in.

For example,

If you want to confine the UnitPrice column >=0, you would use a domain constraint. Although any row that had a UnitPrice that didn’t meet the constraint would be rejected,

The domain is the column, and the constraint is a domain constraint.

You’ll use this kind of constraint when dealing with

·         CHECK constraints,

·         Rules,

·         Defaults, and

·         DEFAULT constraints.

Referential Integrity Constraints

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.

Entity and referential integrity together form key integrity.