Creating Database constraints Part1

Bismillahir Rahmanir Raheem
Now lets talk about constraints.
In oracle sql their are five constraints.

1. Primary key constraint
2. Not null constraint
3. Foreign key constraint
4. Unique key constraint
5. Check Constraint

Most of the students are aware of primary constraint,not null and Foreign key constraint.
We will look at each of them in detail.And how to craete them in database.

Not null constraint --
When we add a not null constraint on a table column it means that it must be assigned a value
when a new row is added to the table


Primary key and Unique constraint--
Primary key constraint uniquely identifies a tuple in a table.Primary key can be composed of one or more table column.
One important thing to note that primary key constraint is not the same as unique constraint.
Unique constraint also uniquely identifies a tuple in table.

So what is the difference between primary key and unique constraint.
Following are the important notes to remember
1. When we create primary constraint on a table column then their must be some value entered in that column
   it can not be null while this is not the case with unique constraint.when you create a unique constraint
   on a table column it can have a null value(means no value).
2. So primary key constraint is combination of two constraints(not null constraint+ unique constraint)  

Foreign key constraint--
Their comes situations in which we require that the value entered in a table column(column1) must be one of those that are in some other table column (column2).This type of constraint is said a foreign key constraint on column1.foreign key (column1) references column1 of table1.
If you don`t understand it clearly look to the below heading where we create foreign key constraint.


Check constraint--
Check constraint when applied on a table column ,then it checks that the value entered in that column
must be one of the one that you have specified while creating check constraint

Now lets See how to create all these constraints in different ways.Their are two ways

1.Creating constraint with Create Table statement
2.Creating constraint with Alter Table statement

Now with both these ways associated two ways one is called in-line and the other one is outof-line approach.
Now you will be wondering what is meant by in-line and outof-line.
In-line means that the constraint is created while defining the column
while in outof-line approach you define constraint after the column definition.

Now lets get ready to this part of constraint creation
Creating constraints with Create table statement--
1. Primary key constraint
   a)in-line
   create table my_table(id number(2) primary key,name varchar2(20));[constraint will be given system     assigned name]
                  or
   create table my_table(id number(2)  constraint cons1 primary key,name varchar2(20));    [constarint is given name cons1]

   You can see that in in-line approach we are creating the constraint with column definition  

   b)outof-line
   create table my_table(id number(2),name varchar2(20) ,primary key(id)); [constraint will be given system assigned name]
                or
   create table my_table(id number(2),name varchar2(20),constraint prime_cons primary key(id)); [constarint is given name prime_cons]
  
2. Foreign key constraint
   a)in-line
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20) references tab1(department));
         or
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20) constraint c1 references tab1(department));   
  
   b) outof-line
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20),constraint c1 foreign key(dept) references tab1(department));
                or
    create table tab1(department varchar2(20) primary key);
    create table tab2(faculty_name varchar2(20),dept varchar2(20),foreign key(dept) references tab1(department));
   
    Now when you insert a row in table tab2, then the value you assign to column dept of tab2 must be one of the values already present
    in column department of tab1
   
3. Check constraint--
   a)in-line
    create table xy(id number check (id in (1,2)));
         or
    create table xy(id number constraint id_cons check(id in (1,2)));
   b)outof-line
   create table xy(id number,check (id in (1,2)) );
            or
   create table xy(id number,constraint id_check check(id in (1,2)));

4. Unique constraint--
   a)in-line
    create table abc(id number unique);
      or
    create table abc(id number constraint id_unique);
    b)outof-line
    create table abc(id number,unique(id));
          or
    create table abc(id number,constraint id_unique unique(id));          
5. Not null constraint--
      a)in-line
      create table op(id number not null);
           or
      create table op(id number constraint id_cons    not null);
     
      b) You can not create not null constraint with outof-line approach
        So you must always create not null constraint while defining the column     


We will see the other way around of creating constraints with Alter table statement or creating constraint after table creation in the next post.