Constraints in Oracle SQL

The methods by which the database provides security and Integrity are the constraints that provide some restriction on data. There are mainly three types of Constraints in Oracle. they are:

• Integrity Constraints
• Domain Constraints
• Referential Constraints

Now we'll look at some keywords which have their impact on the database, as constraints. they are:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

NOT NULL: This constraint provides the restriction that : the data shouldn't be given a NULL, this implies that we shouldn't leave the column empty without giving any value. The syntax for using NOT NULL, is as follows: 

Syntax:  NOT NULL

Example: 

create table Teacher_Details(

         id number(5),

        name varchar2(15),

        class_Room number(3) NOT NULL

);

UNIQUE: This constraint provides the restriction that: the data we enter shouldn't contain duplication, this implies that there should not be any repeated values in the table. the syntax for the above can be in two ways: one straight after the data definition and other after the definitions at the end of the table.

Syntax: constraint UNIQUE

Example:

create table Teacher_Details(

         id number(5),

        name varchar2(15) constraint name_un UNIQUE,

        class_Room number(3) NOT NULL

);

PRIMARY KEY: Its like a combination of UNIQUE and NOT NULL. It provides a facility to the table: it uniquely identifies a row in a table.

syntax: constraint PRIMARY KEY

Example:

create table Teacher_Details(

         id number(5) constraint id_pk PRIMARY KEY,

        name varchar2(15),

        class_Room number(3) NOT NULL

);

FOREIGN KEY: Its like a link between to related tables, that in turn provides a parent child relationship like we see in inheritance. Yes, we can say that Oracle too implements inheritance with the help of foreign key constraint, but regarding other properties: I don't know!

It helps a lot in joining two tables, suppose that we divide students and classes into two tables, we can have a column in common called, class_num say. Now if we want to search for a class then we can join the tables while query using this foreign key. a foreign key attribute can't be deleted if there's an entry in the parent table and viceversa. hence in such cases, we first delete the entry in the child table and then go for the parent one.

Syntax: constraint REFERENCES ()

Example:

//Parent Table
create table Teacher_Details(

         id number(5) constraint id_pk PRIMARY KEY,

        name varchar2(15),

        class_Room number(3)

);

//Child Table

create table Class_Details(

        class_Room number(3) constraint class_fk REFERENCES Teacher_Details(class_Room),

        class_strength number(100),

        class_Status char(2) NOT NULL

);