SQL Constraints in SQL

Let us consider the same library database for understanding this section.

Constraints enforce certain rules on tables or columns of table to ensure the correctness of the data being entering. It ensures, correct values are being entered, no redundant data exists in table, any two entities are related etc. There are table level constraints as well as column level constraints. Some of the column level constraints are discussed below.

NOT NULL

This constraint makes sure that no NULL values are being entered to the columns. By default, columns can have NULL values. If we specify NOT NULL constraint, it will not allow inserting any NULL values to that particular column.

There are two ways of defining NOT NULL constraints. If table is not yet created and if we know that any columns in the table cannot have NULL values then we can define NOT NULL constraints while creating table itself. Below example of creating BOOKS table creates NOT NULL constraint on ISBN, BOOK_NAME and PUBLISHER_NAME columns of the table.

CREATE TABLE BOOKS (ISBN NUMBER (13) NOT NULL PRIMARY KEY,
		BOOK_NAME VARCHAR2 (100) NOT NULL,
		PUBLISHER_NAME VARCHAR2 (50) NOT NULL,
		CATEGORY_NAME VARCHAR2 (30),
		NUM_OF_PAGES NUMBER (4));

OR

CREATE TABLE BOOKS (ISBN NUMBER (13) NOT NULL,
		BOOK_NAME VARCHAR2 (100) NOT NULL,
		PUBLISHER_NAME VARCHAR2 (50) NOT NULL,
		CATEGORY_NAME VARCHAR2 (30),
		NUM_OF_PAGES NUMBER (4),
	PRIMARY KEY (ISBN));

If table is already created and latter we realize that some of its column needs to NOT NULL, then we can use ALTER statement to add constraint on the table like below.

ALTER TABLE BOOKS MODIFY COLUMN ISBN NUMBER (13) NOT NULL;
ALTER TABLE BOOKS MODIFY COLUMN BOOK_NAME VARCHAR2 (100) NOT NULL;
ALTER TABLE BOOKS MODIFY COLUMN PUBLISHER_NAME VARCHAR2 (50) NOT NULL;

NOT NULL constraint is in turn converted into CHECK constraint. It is evident from above example. If a table is already loaded with some data and the column which we are going to make NOT NULL column has any NULL values, then it will not allow us to modify the column to NOT NULL. We have to first remove all the nulls in the column either by adding some dummy values or completing removing such records, whichever is relevant. Then we can use ALTER statement as above to make that column as NOT NULL.

DEFAULT

This constraint gives default values to the column, when values are not entered to that column while inserting a record. If some values are inserted to the column, then it will take that value. Default value can be anything provided it matches with the datatype and size of the column.
This constraint can be created while creating the table itself like below. Here it defaults the FINE_AMOUNT value to zero. It can take any non-zero values if the user enters any non-zero values. If no values are entered, then it assumes the value as zero.

CREATE TABLE BOOK_RETURNS (ISBN NUMBER (13, 0) NOT NULL, COPY_NUM NUMBER (3, 0) NOT NULL, BRWR_ID VARCHAR2 (9 BYTE) NOT NULL, RETURN_DATE DATE NOT NULL, FINE_AMOUNT NUMBER DEFAULT 0, CONSTRAINT BOOK_RETURNS_PK PRIMARY KEY (ISBN, COPY_NUM, BRWR_ID, RETURN_DATE);
If we have to add DEFAULT constraint after table is being created, then we can use ALTER statements like below to modify the table.

ALTER TABLE BOOK_RETURNS MODIFY FINE_AMOUNT DEFAULT 0;

We can specify any value as default value.

ALTER TABLE BOOK_RETURNS MODIFY FINE_AMOUNT DEFAULT 0.1;


Suppose we want to default the city of author to DELHI, then we can specify it as below :

ALTER TABLE AUTHOR MODIFY ADDRESS DEFAULT ‘Delhi’; — this value will be considered if address is not entered while inserting any Author’s record.

We can modify default values latter any other values.

UNIQUE

This constraint defines that column should have distinct values for each records. It does not accept any duplicate value for this column. It is similar to PRIMARY KEY, but not primary key. These UNIQUE columns can act as a Primary key, in the absence of it.

We can create UNIQUE constraint while creating the table or latter by using ALTER statement.

CREATE TABLE BOOKS (ISBN NUMBER (13) NOT NULL PRIMARY KEY,
   BOOK_NAME VARCHAR2 (100) UNIQUE, --now no duplicate names can be entered for this column 
   PUBLISHER_NAME VARCHAR2 (50) NOT NULL,
   CATEGORY_NAME VARCHAR2 (30),
   NUM_OF_PAGES NUMBER (4));

 ALTER TABLE BOOKS ADD CONSTRAINT cnstr_bk_nm UNIQUE (BOOK_NAME);–named constraint

PRIMARY Key

As we discussed already, PRIMARY KEY is the unique key column which uniquely identifies each record in the table. It can be created in several ways shown below.

CREATE TABLE BOOKS (ISBN NUMBER (13) PRIMARY KEY,–Usual method of defining primary key
        BOOK_NAME VARCHAR2 (100),
        PUBLISHER_NAME VARCHAR2 (50),
        CATEGORY_NAME VARCHAR2 (30),
        NUM_OF_PAGES NUMBER (4));

CREATE TABLE BOOKS (ISBN NUMBER (13),
BOOK_NAME VARCHAR2 (100),
PUBLISHER_NAME VARCHAR2 (50),
CATEGORY_NAME VARCHAR2 (30),
NUM_OF_PAGES NUMBER (4),
CONSTRAINT pk_isbn PRIMARY KEY (ISBN)); –Usually used when more than one column is involved in primary key, and defines a named primary key constraint

— ALTER statements are used when table is already created
ALTER TABLE BOOKS ADD PRIMARY KEY (ISBN);

ALTER TABLE BOOKS ADD CONSTRAINT pk_isbn PRIMARY KEY (ISBN);

When ALTER statements are used, care should be taken that column has unique values. Otherwise it will not allow creating primary key on that column.

FOREIGN Key

This constraint ensures the relational mapping between any two table and hence the relational integrity between the tables. It establishes parent – child relationship between any two tables.

There are several ways of defining foreign keys on table :

CREATE TABLE BOOK_AUTHOR (AUTHOR_ID VARCHAR (7) NOT NULL PRIMARY KEY,
 ISBN NUMBER (13) NOT NULL
CONSTRAINT fk_BA_AUTHID FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR (AUTHOR_ID),
CONSTRAINT fk_BA_ISBN FOREIGN KEY (ISBN) REFERENCES BOOKS (ISBN));

 — used when table is already created

ALTER TABLE BOOK_AUTHOR
    ADD CONSTRAINT fk_BA_AuthID
  FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR (AUTHOR_ID);

ALTER TABLE BOOK_AUTHOR
    ADD CONSTRAINT fk_BA_ISBN
  FOREIGN KEY (ISBN) REFERENCES BOOKS (ISBN);

Here AUTHOR and BOOKS tables are known as parent tables, whereas BOOK_AUTHOR is known as child table. In order to load child table, parent table should be loaded with details. For example, we cannot load BOOK_AUTHOR table for which either AUTHOR or BOOK does not exist in respective tables.

CHECK Constraints

This constraint is used to load correct values into the column. It makes sure that no incorrect data is being loaded. For example in the AGE column, one cannot insert dates or some negative values. Some positive value needs to be entered. Such checks are done by CHECK constraint. It can be defined while creating the table or latter by altering the table.

–It checks positive value for copy is being entered.

CREATE TABLE BOOK_LOAN (BRWR_ID VARCHAR (8) NOT NULL,
ISBN NUMBER (13) NOT NULL,
BOOK_CP_NUMBER NUMBER (3) NOT NULL CHECK (BOOK_CP_NUMBER >0),
 LOAN_DATE DATE NOT NULL, DUE_DATE DATE,
PRIMARY KEY (BRWR_ID, ISBN, LOAN_DATE));

CREATE TABLE BOOK_LOAN (BRWR_ID VARCHAR (8) NOT NULL, 
ISBN NUMBER (13) NOT NULL, 
BOOK_CP_NUMBER NUMBER (3) NOT NULL,
 LOAN_DATE DATE NOT NULL, DUE_DATE DATE, 
PRIMARY KEY (BRWR_ID, ISBN, LOAN_DATE),
CONSTRAINT chk_CP_NUM CHECK (BOOK_CP_NUMBER >0)); --Named constraint

 ALTER TABLE BOOK_LOAN ADD CONSTRAINT chk_CP_NUM CHECK (BOOK_CP_NUMBER >0);

Above examples shows how to add check constraints on single column. We can have check constraints on more than one column like below :

CREATE TABLE BOOKS (ISBN NUMBER (13) NOT NULL CHECK ISBN>0 PRIMARY KEY,
		BOOK_NAME VARCHAR2 (100) UNIQUE,
		PUBLISHER_NAME VARCHAR2 (50) NOT NULL,
		CATEGORY_NAME VARCHAR2 (30) CHECK CATEGORY_NAME = ‘Database’,
		NUM_OF_PAGES NUMBER (4) CHECK NUM_OF_PAGES >= 0);

OR

CREATE TABLE BOOKS (ISBN NUMBER (13) NOT NULL PRIMARY KEY,
		BOOK_NAME VARCHAR2 (100) UNIQUE,
		PUBLISHER_NAME VARCHAR2 (50) NOT NULL,
		CATEGORY_NAME VARCHAR2 (30),
		NUM_OF_PAGES NUMBER (4)
CONSTRAINT chk_cnstr (NUM_OF_PAGES >= 0 AND ISBN>0 AND CATEGORY_NAME = ‘Database’));

Here CATEGORY_NAME = Database makes sure only database books are entered into BOOKS table. It will not insert any other books into this table.

DROP Constraints

Like we created constraints above, it can also be deleted / dropped if it is no more required. We can use ALTER statement to drop the constraints that are created on a table. Some of the methods of dropping the constraints are shown below :

ALTER TABLE BOOKS DROP PRIMARY KEY; -- dropping primary keys
ALTER TABLE AUTHOR DROP CONSTRAINT fk_BA_AUTHID; -- dropping foreign keys
ALTER TABLE BOOK_LOAN DROP CONSTRAINT chk_CP_NUM; -- dropping check constraint
ALTER TABLE BOOKS DROP CONSTRAINT chk_cnstr; -- dropping any named constraint