Question

Write the different types of constraints and their functions.

Answer 1

The different types of constraints are:

1. Unique Constraint
2. Primary Key Constraint
3. Default Constraint
4. Check Constraint.
5. Table Constraint:

1. Unique Constraint:

• This constraint ensures that no two rows have the same value in the specified columns.
• For example, UNIQUE constraint applied on Admno of student table ensures that no two students have the same admission number and the constraint can be used as:

CREATE TABLE Student:
(
Admno integer NOT NULL UNIQUE,→ Unique constraint
Name char (20) NOT NULL,
Gender char (1),
Age integer,
Place char (10)
);

• The UNIQUE constraint can be applied only to fields that have also been declared as
  NOT NULL.
• When two constraints are applied on a single field, it is known as multiple constraints.
• In the above Multiple constraints, NOT NULL and UNIQUE are applied on a single field Admno, the constraints are separated by a space and at the end of the field definition, a comma(,) is added.
• By adding these two constraints the field Admno must take some value (ie. will not be NULL and should not be duplicated).

2. Primary Key Constraint:

• This constraint declares a field as a Primary key which helps to uniquely identify a record.
• It is similar to a unique constraint except that only one field of a table can be set as the primary key.
• The primary key does not allow ULI values and therefore a field declared as the primary key must have the NOT NULL constraint.

CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint
Name char(20)NOT NULL,
Gender char(1),
Age integer,
Place char(10)
);

• In the above example, the Admno field has been set as the primary key and therefore will
• help us to uniquely identify a record, it is also set NOT NULL, therefore this field value cannot be empty.

3. Default Constraint:

• The DEFAULT constraint is used to assign a default value for the field.
• When no value is given for the specified field having a DEFAULT constraint, automatically the default value will be assigned to the field.
• Example showing DEFAULT Constraint in the student table:

CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARYKEY,
Name char(20)NOT NULL,.
Gender char(1),
Age integer DEFAULT = “17”, → Default Constraint
Place char(10)
);

In the above example, the “Age” field is assigned a default value of 17, therefore when no value is entered in age by the user, it automatically assigns 17 to Age.

4. Check Constraint: 

• Check Constraint helps to set a limit value placed for a field.
• When we define a check constraint on a single column, it allows only the restricted values on that field.
• Example showing check constraint in the student table:

CREATE TABLE Students
(
Admno integer NOT NULL PRIMARYKEY,
Name char(20)NOT NULL,
Gender char(l),
Age integer (CHECK< =19), —> CheckConstraint
Place char(10)
);

In the above example, the check constraint is set to the Age field where the value of Age must be less than or equal to 19.
The check constraint may use relational and logical operators for conditions.

Table Constraint:

• When the constraint is applied to a group of fields of the table, it is known as the Table constraint.
• The table constraint is normally given at the end of the table definition.
• For example, we can have a table namely Studentlwith the fields Admno, Firstname, Lastname, Gender, Age, Place.

CREATE TABLE Student 1:
(
Admno integer NOT NULL,
Firstname char(20),
Lastname char(20),
Gender char(1),
Age integer,
Place char(10),
PRIMARY KEY (Firstname, Lastname) —-Table constraint ‘
);

In the above example, the two fields, Firstname and Lastname are defined as Primary key which is a Table constraint.