SQL Constraints:




The constraints are the rules that are applied when storing some data into the table. The SQL provides integrity constraints that are used to ensure the integrity of data that if the data is consistent and accurate to be stored in the table or not.




Constraints or integrity constraints are one and the same things. There are three categories in which the constraints can be divided:

  1. Table related constraints: these types of constraints are defined in the definition of the table. The constraints can also be referred as the elements in a table. The constraints that are defined in a table can only be applied on one or more columns.
  2. Assertions: we can relate an assertion to one or more tables. The constraints related to assertion are defined within the assertion definition that is separate from the definition of the table.
  3. Domain constraints: the domain constraints are defined within the domain definition that is separate from the definition of the table. This is associated with the columns that are defined within a defined domain.

The table related constraints are the most common and can be divided into further two categories that are: table constraints and column constraints. The table constraints are referred as the element of the table and column constraints are defined within the definition of the column.

Following are some of the constraints that are used in the SQL.

  1. NOT NULL
  2. Unique
  3. Foreign key
  4. Primary key
  5. Check
  6. Default

Consider the following diagram:

used-in-the-sql

 

Not Null Constraints

The NOT NULL constraint is used to restrict a column from having a null value. If the NOT NULL constraint is applied to any of the columns of then the user cannot pass any null value to that column. NOT NULL constraint specifies that the column should have a definite proper value. We can use the NOT NULL constraint as only the column constraint. It cannot be used at table level. The syntax of using NOT NULL constraint is as follows:

<Column name> {<data type> | <domain>} NOT NULL

 

EXAMPLE using NOT NULL constraint

CREATE table employee (DEPT_NO int NOT NULL, EMP-Name char (20))

This query will declare that the field DEPT_NO cannot be assigned a null value.

 

UNIQUE Constraint

The unique constraint is supported by both the column constraints and the table constraints. Unique constraint is of two types that is primary key and unique key.

The unique constraint is used to allow that the column or set of columns should have unique and different values. It means that the values will be different from other values in the other rows of the same table. There will be no duplicate data when the unique constraint is used. The syntax of unique constraint is as follows:

<Column name> {<data type> | <domain>} UNIQUE

 

EXAMPLE using unique constraint when creating a table (table level)

CREATE table employee (DEPT_NO int NOT NULL UNIQUE, EMP_NAME char (20))

In this query we have specified that the DEPT_NO will have a unique and different value of integer type and this value cannot be a null value.

 

EXAMPLE using unique constraint when creating a table (column level)

Use the following query to use the unique constraint at column level:

ALTER table employee add unique (DEPT_NO);

This query demonstrates that the field DEPT_NO of employee table will have a unique value.

 

Primary key Constraint

The primary key constraint is a type of SQL unique constraint. The primary key constraint also allows only unique values to be entered into one or more columns of a table. There are two restrictions that are applied to the primary key constraint:

  1. The column that is defined with the primary key constraint cannot contain the NULL values.
  2. We can define only one primary key constraint for each of the table.

Users use the primary key constraint to index the data that is contained by the table. The syntax of primary key constraint is as follows:

<Column name> {<data type> | <domain>} PRIMARY KEY

 

EXAMPLE using primary key constraint at table level

Use the following query to use the primary key constraint at table level:

CREATE table employee (DEPT_NO int primary key, EMP_NAME char (20) NOT NULL);

This query will create a table first named employee and then will create the primary key on the field DEPT_NO. It also specifies that the field EMP_NAME cannot have a null value.




Example using Primary key at column level

Use the following query to use the primary key constraint at table level:

Alter table employee add PRIMARY KEY (DEPT_NO);

This query will create the primary key on the field DEPT_NO.

 

Foreign Key constraints

The foreign key demonstrates that how the data in one table will be related to other data in another table. This is the reason the foreign key constraint is also known as referential constraint. The tables in a relational database are linked together. This is because to ensure the integrity of data.

This generates relationships between the tables providing referential integrity between the tables. In short we can say that the Foreign Key is used to establish a relation between two tables or to relate tables.

The foreign key provides an advantage that it does not allow the destruction of links that are established between the tables. When you are creating the foreign key constraint you must define a UNIQUE or a PRIMARY key on the referenced columns.

We can also create the foreign key constraint as both the table constraint and the column constraint. Consider the following example to understand the use of foreign key constraint.

EXAMPLE

A foreign key is a key that has the same value as the primary key in another relation. A dependent or child table is a table in which the foreign key is created. The Parent table is a relation to which the foreign key is referred. The key is used to connect one relation to the other relation, when there is established a relationship between two tables. A relation may have many foreign keys.

The following figure shows two relations. The RollNo attribute in Parent relation is used as primary key. The RollNo attribute in child relation is used as foreign key. It refers to RollNo attribute in Parent relation.

example
In the above example, in the parent table the primary key is the roll number and in the child table the roll number field is set as the foreign key. The values in the field RollNo that are added in the child table must also be entered into the parent table where the RollNo is a primary key. The advantage is that the user cannot enter an invalid data.

 

EXAMPLE using foreign key constraint at table level

Suppose you have a table named student as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

And a table named Student-Result as follows:

SUBJECT_ID STUD_ID Subject Marks
204 100 English 56
230 100 Math 78
144 140 Computer 89
230 110 Math 84
204 110 English 68
144 190 Computer 94
204 150 English 64
144 150 Computer 76

Use the following query to use the foreign key constraint at table level:

Create table student-result (SUBJECT_ID int primary key, STUD_ID foreign key references student (STUD_ID));

In this query SUBJECT_ID is made the primary key of the student-result table and the STUD_ID field id made the foreign key which reference to the STUD_ID column of the student table.

 

EXAMPLE using foreign key constraint at column level

Use the following query to use the foreign key constraint at the column level:

Alter table student-result add foreign key (STUD_ID) REFERENCES student (STUD_ID);

This query will make the STUD_ID column of the student-result table as the foreign key which references to the column STUD_ID of the student table.

 

Behavior of Foreign Key column on delete

The foreign key can be deleted by using two methods that are on delete cascade and on delete null. The ON DELETE CASCADE is used to delete the record from the child table by deleting the value of foreign key from the parent table. The cascade is used to update or delete a data from a certain column of the table.

The ON DELETE NULL is used to set all the values to NULL that are in the child table if these values are deleted from the parent table this means that when the user deletes the record from the parent table the corresponding values in the child table will be set NULL.

If the user does not use any of these methods and wants t delete the data from the parent table then an error will occur. The error is given below:

Error: Record in child table exist

 

Check constraint

The check constraint is useful when the user wants to limit the range of value that can be occurred in a certain column. The check constraint is also used to set a condition so that when a value is entered into a column it must meet the condition first.

The check constraint is used to maintain the domain integrity. The check constraint that is defined on one table only refers to the columns of that table and does not include the columns of other tables.

 

EXAMPLE using check constraint at table level

Use the following query to use the check constraint at the table level:

Create table employee (employee-id int NOT NULL CHECK (employee-id > 0), employee-name char (30) NOT NULL);

This query has set a condition that the values that are entered in the employee-id column should be positive values. In other words the values that are entered into the column employee-id of student table should be greater than 0.




EXAMPLE using check constraint at column level

Use the following query to use the check constraint at the column level:

Alter table employee add check (employee-id > 0);