Constraints and Joins

Dear Friends

Let's understand the rule of constraints in oracle.

Constraint:- It is the rule which restrict the incorrect data to be inserted into the database.

Types of Constraint

1.    Not Null Constraint

2.    Unique  Constraint

3.    Check  Constraint

4.    Primary Key Constraint

5.    Foreign Key Constraint

6.    Ref Key(Referential Kay) Constraint

TABLE LEVEL CONSTRAINT:-

COLUMN LEVEL CONSTRAINT:-

1.       NOT NULL:-This is the constraints will avoid null values to the column specified.

NOTE:- It is the only  constraint cannot be defined at Table level.

2.    UNIQUE CONSTRAINT:- This is the constraint which will avoid duplicate values to the column specified. It can be specified at column level as well as table level. 

3.    CHECK CONSTRAINT:-This is the constraint which will be used for setting few conditions on the specific column. Even this can be defined at table and column level.

4. Primary Key Constraint:-This is the constraints which is combination of Not null and unique key constraint.

5.    Foreign Key Constraint:- It is also called as Referential Integrity Constraint which designates a column as foreign key and establish a relation between the FOREIGN KEY and a specified Primary or unique Key.

Below is the example which shows and contains all constraints.

Syntax:-Create table <table_name>

(Empno NUMBER Constraint Empno_NN NOT NULL,

ENAME VARCHAR2(30) NOT NULL

);

SQL>  create table emp_info

  2   (

  3   empno number Constraint empno_pk Primary key,

  4   ename varchar2(20) Not Null,

  5   Job varchar2(20)   Constraint job_NN Not Null,

  6   MGR NUMBER Constraint mgr_ref References emp_info(empno),

  7   HIREDATE  Date,

  8   Sal Number Constraint sal_chk CHECK (sal Between 500 and 5500),

  9   comm Number,

 10   Deptno Number,

11  Constraint deptno_fk FOREIGN KEY(deptno) References dept(deptno)

12  );

  Data Dictionary:-User_Constraints , User_cons_columns

Join:- It is the query which combines with more than one table under certain condition. 

Types of Join:

1.       Equi Join

2.       Non Equi Join

3.       Self Join

4.       Outer Join

Join Condition:-It's join which contains WHERE clause comparing two columns each from a different table. 

Note:-To Join 'N' tables together,We need a minimum of 'n-1' join condition

Equi Join:- It's a join with a join condition containing an equality Operator.

SQL>SELECT e.*,d.* FROM EMP e ,DEPT d WHERE e.deptno=d.deptno;

Non Equi Join:-It's a join condition that is executed when no column in one table correspond directly to a column in the other table.

SQL>SELECT e.ename,e.sal,s.grade FROM EMP e,SALGRADE s WHERE e.sal between s.LOWSAL and s.HISAL;

Outer Joins:-An outer join extends the result of a simple or inner join.It retuns all the rows that satisfy the join condition and also those rows from one table which no rows from other table satisfy the join condition.

SQL>SELECT e.*,d.* FROM EMP e ,DEPT d WHERE e.deptno(+)=d.deptno;

Self Join:-It is a join of a table to itself .the same table appears twice in FROM clause and is followed by the table Aliases.

SQL>SELECT e1.ename “EMPLOYEE NAME”,

            E2.ename “MANAGER NAME”

FROM EMP e1 ,EMP e2 WHERE e1.mgr=e2.empno;

 

Thanks for reading my blog .You are welcome to share your thoughts.

Thanks
Ghanshyam M