This tutorial highlights the need for applying appropriate table constraints and the different types of constraints.

Problem Description


A database contains many tables. There is a need to define the relationships between the tables. These tables contain a lot of data, sometimes sensitive information. Certain business rules need to be enforced for the smooth functioning of the database machinery. To maintain integrity between values of all tables that are related to each other as well as to enforce certain business rules, some constraints need to be defined and applied to the tables.

Operation


Appropriate table constraints are specified on the tables to define the relationship between the tables as well as maintain data integrity.

Solution


A constraint is a rule that is used for optimization purposes. Database constraints are rules that define interrelations between tables and can check and modify the data in a database. These rules are realized as special database objects. The main advantage of using constraints consists in capability to implement data check, and a part of business-logic of the application at a level of a database, i.e. to centralize and implify it, so as to make the development of databases applications easier and more reliable.

There are five types of constraints :

  1. A NOT NULL constraint is a rule that prevents null values from being inserted into the tables
  2. A unique constraint (also referred to as a unique key constraint) is a rule that forbids duplicate values in one or more columns within a table. Unique and primary keys are the supported unique constraints
  3. A primary key constraint is a column or combination of columns that has the same properties as a unique constraint. You can use primary key and foreign key constraints to define relationships between table
  4. A foreign key constraint (also referred to as a referential constraint or a referential integrity constraint) is a logical rule about values in one or more columns in one or more tables. This constraint prevents insert, update, or delete operations that would otherwise result in missing information
  5. A table check constraint sets restrictions on data added to a specific table. For example, a table check constraint can ensure that the salary level for an employee is at least $20,000 whenever salary data is added or updated in a table containing personnel information
  6. An informational constraint is an attribute of a certain type of constraint, but one that is not enforced by the database manager