Identifying Constraints

Types of Constraints:

• PRIMARY KEY

  The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces uniqueness of the column or column combination. It ensures that no column that is part of the primary key can contain a null value. A null value is a value that does not exist. For example, in the DEPENDENTS table, the column ID is the primary key.This column will not allow either a duplicate value or a null value.

• FOREIGN KEY

  The FOREIGN KEY constraint designates a column or a combination of columns as a foreign key. It establishes a relationship between a primary key or a unique key in the same table or different table. A foreign key enforces that the value within the column matches the value in the relationship column. For example, the RelativeId column in the DEPENDENTS table refers to the EMPLOYEES table. You cannot delete a record in the EMPLOYEES table whose RelativeId is used in the DEPENDENTS table. Also, with a non-existing RelativeId in the EMPLOYEES table, you cannot insert a record into the DEPENDENTS table.

• CHECK

  The CHECK constraint enforces integrity by restricting the values to be inserted in a column. It defines a condition that each row must sastisfy. You can define multiple check constraints on a single column. Also, you can apply a single check constraint to multiple columns. For example, when you impose the CHECK constraint on the Gender column in the DEPENDENTS table, you can specify that the only valid values are either M or F.

• UNIQUE

  The UNIQUE constraint requires that no two rows of a table can have duplicate values in a specified column or a set of columns. A table can have more than one unique key. If the UNIQUE constraint comprises more than one column, then the group of columns is called a coomposite key. For example, you can impose the UNIQUE constraint on the Email column of the EMPLOYEES table. You ensure that each employee has a unique email ID.

• NOT NULL

  The NOT NULL constraint ensures that the column contains no null values. After you define a column as not null, it becomes a mandatory column. Columns without the NOT NULL constraint can contain null values by default. For example, you can ensure that the last names of all the dependents do not hold the null values. You can achieve this by imposing the NOT NULL constraint on the LastName column of the DEPENDENTS table.