Database Constraints

Table of Contents

Constraints

Constraints are the conditions forced on the columns of the table to meet the data integrity. We have seen above what types of data integrities exists in the database. Now let see what constraints can be applied on tables so that data integrity is met.

NOT NULL

This constraint forces the column to have non-null value. We cannot enter/update any NULL value into such columns. It must have valid value all the time. For example, each student in STUDENT table should have class specified. No student can exist without class. Hence class column in the STUDENT table can be made NOT NULL.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);

UNIQUE

This constraint ensures, the column will have unique value for each row. The column value will not repeat for any other rows in the table.

Passport number of individual person is unique. Hence passport column in the PERSON table is made UNIQUE. It avoids duplicate entry of passport number to other persons.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL UNIQUE
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER
CONSTRAINT uc_StdID UNIQUE (STUDENT_ID));

PRIMARY KEY

This constraint is another type of UNIQUE constraint. This constraint forces the column to have unique value and using which, we can uniquely determine each row.

As we have seen in STUDENT example, STUDENT_ID is the primary key in STUDENT tables.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL PRIMARY KEY,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER);

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER
CONSTRAINT pk_StdID PRIMARY KEY (STUDENT_ID));

FOREIGN KEY

This constraint helps to map two or more tables in the database. It enforces parent-child relationship in the DB. Foreign key in the child table is the column which is a primary key in the parent table.

For example, each employee works for some department. Hence to map employee and department tables, we have to have DEPARMENT_ID of DEPARTMENT table in EMPLOYEE table too. DEPARTMENT_ID is the primary key in DEPARTMENT table (Parent table) and is foreign key in EMPLOYEE table (Child table).

CREATE TABLE EMPLOYEE (EMPLOYEE_ID VARCHAR2 (10) PRIMARY KEY,
EMP_NAME VARCHAR2 (50),
DOB DATE,
……
DEPT_ID NUMBER
CONSTRAINT fk_DeptId FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID));

CHECK

This constraint is used to check for specific conditions on the column. For example, if age has to be entered between 25 and 32, we can use CHECK Constraint. This will not allow to enter the age<25 and age>32.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER CHECK (AGE >= 25 and AGE<= 32));

DEFAULT

This constraint specifies the default value to be entered when no value is entered to it. Suppose whenever we enter an entry in the STUDENT table, apart from Student details we also have to store the date when it is being entered. This entry would always be SYSDATE. Instead of entering it each time when we do an entry, if we set the default value of this column as SYSDATE, this column will be always inserted with SYSDATE. If we need to override this value with any other date, then we have to explicitly insert the new date.

CREATE TABLE STUDENT (STUDENT_ID NUMBER (10) NOT NULL,
STUDENT_NAME VARCHAR2 (50) NOT NULL,
AGE NUMBER,
….
CREATED_DATE DATE DEFAULT SYSDATE);

Creating Constraints and Keys in SQL

As we saw above constraints are used on the table to make sure the records and attributes entered are correct in that contest. It makes sure that there is no incorrect data being entered. If there is any mismatch or wrong data being entered, then the transaction will be rejected. How do we create all these constraints in SQL? Either they are created when we create the table for the first time or it can be constructed after table is being created.

The general syntax for creating a constraint when a table is created is as shown below.

CREATE TABLE table_name (
Column1 DATATYPE (SIZE) CONSTRAINT_TYPE,
Column2 DATATYPE (SIZE) CONSTRAINT_ TYPE,
….
); –where CONSTRAINT_TYPE can be NOT NULL, PRIMARY KEY, CHECK, DEFAULT etc.

Few examples of creating constraints as above is given below:

CREATE TABLE STUDENT (
STD_ID NUMBER (10) NOT NULL PRIMARY KEY,
STD_NAME VARCHAR2 (255) NOT NULL,
ADDRESS VARCHAR2 (255),
DATE_OF_BIRTH DATE); — Example of NOT NULL and PRIMARY KEY Constraint

CREATE TABLE PERSON (
SSN_NUM NUMBER (10) NOT NULL UNIQUE,
PERSON_NAME VARCHAR2 (255) NOT NULL,
ADDRESS VARCHAR2 (255),
AGE NUMBER NOT NULL CHECK (AGE>=18),
LICENCE NUMBER,
DEPT_ID NUMBER DEFAULT 10); — Example of NOT NULL, UNIQUE, DEFAULT AND CHECK Constraint

For a given column, single or multiple constraints can be applied. Constraints can be created on the existing tables as below.

ALTER TABLE table_name ADD CONSTRAINT constraint_name CONSTRAINT_TYPE;

ALTER TABLE PERSON ADD CONSTRAINT checkAge CHECK (AGE >= 18);

ALTER TABLE STUDENT ADD CONSTRAINT stdPrimaryKey PRIMARY KEY (STD_ID);

ALTER TABLE STUDENT ADD CONSTRAINT stdUniqueKey UNIQUE (STD_ID);

ALTER TABLE STUDENT ADD CONSTRAINT stdUniqueKey NOT NULL (STD_NAME);

Drop the Constraint

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE PERSON DROP CONSTRAINT checkAge;

ALTER TABLE STUDENT DROP CONSTRAINT stdPrimaryKey;

ALTER TABLE STUDENT DROP CONSTRAINT stdUniqueKey;

Create Foreign Key Constraint

Creating a foreign key constraint is little different. In order to create a foreign key constraint, we need to have parent table created first. Then, when we create child table, we can have foreign key constraint mentioned as below.

CREATE TABLE table_name (

Column1 DATATYPE (SIZE),

Column2 DATATYPE (SIZE)

……

CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name));

Example:

CREATE TABLE EMPLOYEE (EMPLOYEE_ID VARCHAR2 (10) PRIMARY KEY,

EMP_NAME VARCHAR2 (50),

DOB DATE,

……

DEPT_ID NUMBER

CONSTRAINT fk_DeptId FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID));