EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer  to execute a SQL statement.
EXPLAIN PLAN takes less than a minute to EXPLAIN a query that takes four hours to run because it does not actually execute the SQL statement, it only outlines the plan to use and inserts this execution plan in an Oracle table (PLAN_TABLE).

Why we will use EXPLAIN PLAN without TRACE?
The statement is not executed; it only shows what will happen if the statement is executed. 
When do you use EXPLAIN without TRACE?
When the query will take exceptionally long to run.

How to use EXPLAIN PLAN?

1. Create PLAN TABLE: Execute the script "utlxplan.sql". File location is below

oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql    

2. EXPLAIN Query: Run the EXPLAIN PLAN for the query to be optimized 

EXPLAIN PLAN FOR
select ename,sal,empno,deptno
from emp
where deptno=10;    

Using Tag:

EXPLAIN  PLAN FOR

SET STATEMENT_ID='SQL1'

select ename,sal,empno,deptno
from emp
where deptno=10;

3. PLAN Table is populated: Select the output from PLAN TABLE

select operation, options, object_name, id, parent_id
from plan_table
where statement_id = 'SQL1'   

OR we can use below query to see the output in proper format 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);    

It shows the following information: 

• The row source tree is the core of the execution plan. 
• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation.

In addition to the row source tree, the plan table contains information about the following: 

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query. 

Query processing can be divided into 7 phases :

• Syntactic          : Checks the syntax of the query
• Semantic          : Checks that all objects exist and are accessible
• View Merging  : Rewrites query as join on base tables as opposed to using views
• Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
• Optimization  : Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path.  With the Cost Based  Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
• QEP Generation    : QEP = Query Evaluation Plan.
• QEP Execution      : QEP = Query Evaluation Plan.

In Toad how we will work on Explain plan :

Using toad , this can be achieved by following the steps below.

• Connect to the Oracle SID
• Open a SQL editor, and write the SQL query for which the explain plan is required.
• CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
• Analyze the cost of the query , and identify the areas which are causing the cost to grow high.Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
• This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

TABLE PARTITIONING

Dividing the rows of a single table into multiple parts is called Partitioning of a table.
Partitioning  is useful for a large tables only(Tables greater than 2 GB should always
be considered as candidates for partitioning).

Goals Behind Partitioning

• The performance of queries against the tables can improve.
• The management of the table became easier.
• The backup and recovery operation can be performed better.
• It is easier to load and delete data in partitions than in the large table.

Type of  Table Partition

1. Range Partition Table
2. List Partition Table
3. Hash Partition Table

1. Range Partition Table:-

• The table is divided in ranges(data ranges).
• The Range partition works on filters like greater than ,less than and between operator.
• Used when there are logical ranges of data.

Step to create Range Partition Table:-

• Table cretion

    CREATE TABLE EMP_DETAILS
      (
       EMPNO       NUMBER (4),

       ENAME       VARCHAR2 (10),

      JOB              VARCHAR2 (9),

      HIREDATE   DATE,

      DEPTNO     NUMBER (2)
     )

   PARTITION BY RANGE(DEPTNO)
     (
      PARTITION  PTNDEPTNO10 VALUES LESS THAN (20),
      PARTITION PTNDEPTNO20 VALUES LESS THAN  (30),
      PARTITION PTNDEPTNOMAX  VALUES LESS THAN(MAXVALUE)
    );

• Now insert below data into the EMP_DETAILS table

BEGIN

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7369, 'SMITH', 'CLERK', TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7499, 'ALLEN', 'SALESMAN', TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7521, 'WARD', 'SALESMAN', TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7566, 'JONES', 'MANAGER', TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7654, 'MARTIN', 'SALESMAN', TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7698, 'BLAKE', 'MANAGER', TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7782, 'CLARK', 'MANAGER', TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7788, 'SCOTT', 'ANALYST', TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7844, 'TURNER', 'SALESMAN', TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7876, 'ADAMS', 'CLERK', TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7900, 'JAMES', 'CLERK', TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30);

Insert into EMP_DETAILS(EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values(7902, 'FORD', 'ANALYST', TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 20);

Insert into EMP_DETAILS (EMPNO, ENAME, JOB, HIREDATE, DEPTNO)
 Values (7934, 'MILLER', 'CLERK', TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);

COMMIT;
END;

• Now check how many partition created for this EMP_DETAILS table

         SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS
         FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMP_DETAILS';

• Now Fetch the date from your partition table

          SELECT * FROM EMP_DETAILS PARTITION(PTNDEPTNO10);

2. List Partition Table:-

• Rows map to Partition by a specific list.
• Used to list together unrelated data into partitions.

Step to create List Partition Table:-

• Table creation 

  CREATE TABLE EMP_DETAILS_LIST
     (
     EMPNO      NUMBER (4),
     ENAME      VARCHAR2 (10 BYTE),
     JOB        VARCHAR2 (9 BYTE),
     HIREDATE   DATE,
     DEPTNO     NUMBER (2)
    )
  PARTITION BY LIST(JOB)
   (
   PARTITION MGR_PRE01 VALUES ('MANAGER','PRESIDENT','ANALYST'),
   PARTITION SAL_CLE VALUES ('CLERK','SALESMAN')
  );

• Now insert above set of data into the EMP_DETAILS_LIST table by changing the table name.

• Now Fetch the date from your partition table

         SELECT * FROM  EMP_DETAILS_LIST PARTITION(SAL_CLE);

3. Hash Partition Table:-

1. Some time it may not possible to define the range or list in partition in such case hash partition is useful. 
2. Used to spread data evenly over partitions 
3. All the Hash partition hold same number of data.

Step to create Hash Partition Table:-

• Table creation 

      CREATE TABLE EMP_DETAILS_HASH
          (
           EMPNO      NUMBER (4),
           ENAME      VARCHAR2 (10 BYTE),
           JOB        VARCHAR2 (9 BYTE),
           HIREDATE   DATE,
           DEPTNO     NUMBER (2)
          )
     PARTITION BY HASH(ENAME)
     PARTITIONS 5;

• Now insert above set of data into the EMP_DETAILS_HASH table by changing the table name.
• Now check how many partition created for this EMP_DETAILS table

          SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS

          FROM USER_TAB_PARTITIONS 

          WHERE TABLE_NAME='EMP_DETAILS_HASH';

• Now check how many partition created for this EMP_DETAILS tableNow Fetch the date from your partition table

        SELECT * FROM EMP_DETAILS_HASH PARTITION(SYS_P29);



I am trying to add a new partition in existing table:-

1. create a PARTITIONED table
2. load the NON-PARTITIONED data into the PARTITIONED table
3. drop the NON-PARTITIONED table
4. rename PARTITIONED table to whatever is appropriate

or:

1. export table and data
2. drop table
3. create new partitioned table the way you want it

4. import table data into new partitioned table (this should also create all the necessary indexes and constraints from your original table)

Dropping a table Partition:-

ALTER TABLE Table_Name DROP PARTITION Partition_Name;

CREATING INDEXES UPON PARTITIONS:-

Once a partition a table created then we need to create an index upon that table.

The index may be partitioned according to the same range of values(which we used for partition)

The LOCAL key word tells oracle to create index for each partitions for table.

The GLOBAL keyword tells to oracle to create a non partition index .

Steps to create the index

CREATE INDEX index_name ON table_name(clolumn_name)

LOCAL(PARTITION partittion_name1,...,partittion_name); 

Oracle JOINS

• Join is a query that combines rows from two or more table or views.
• Join is performed whenever multiple tables appear in the FROM clause.
• The common column name with in the table should qualify all references.

Join Condition:

• Many Join queries contain WHERE clause , which compares two columns, each column from different table.
• The column in the join condition need not be part of the SELECT list.
• The LOB Columns cannot be specified in the WHERE clause, when the where clause contains any join.

Guidelines:

• To join n tables together, we need minimum of [n-1] join condition.
• If same column name appears in more than one table , the column name must be prefix with the table name.

Qualifying Ambiguous Column Names:

• The name of the column should be Qualified in the WHERE clause with the table name to avoid the Ambiguous.
• If there is no common column name between two tables than Qualified is not necessary but it is better.

Example:

SELECT ENP.ENAME,EMP.EMPNO,DEPT.DEPTNO
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

Table Aliases:

• Table Alias is nothing but alternate name of table.
• Table Alias is specified in the FORM clause.

Example:

SELECT E.ENAME,E.EMPNO,D.DEPTNO
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Guidelines:

• Table alias max length is 30 characters.
• A table alias should be meaningful and should be maintained as short as possible.
• Table Alias is valid only for that current SELECT statement.

Columns Alias:

• Temporary name of a column.
• Column alias used to make column names more readable.
• Alias is valid only for that current SELECT statement.

Example:

SELECT E.ENAME as Employee_Name,E.EMPNO as EmployeeNumber,D.DEPTNO as Department_Number
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

Type Of Joins:

1. Cartesian Product
2. Simple join or Inner join or Equi join
3. Self join
4. Non Eqie join
5. Outer join

More Practice  

Object Oriented Concepts in SQL

Object Table

1. Object Table created by User Define Data type.
2. Each row of the Object Table has an object identifier(OID) , which is unique through out the database.
3. Object Table automatically inherit the data type from user define data type. 
4. we can perform SELECT,INSERT,UPDATE and DELETE operation in Object Table.If Object table having REF constraint then DML operation is not possible.

User Define Data Tyepe

1. User Define Data Type is schema object of database .
2. Data Dictionaries  USER_TYPE/USER_OBJECTS.

Follow the Below Steps 

1. Creating User Define Data Type

CREATE OR REPLACE TYPE DEPT_DETAILS AS OBJECT(DEPTNO NUMBER,DNAME VARCHAR2(400),LOC VARCHAR2(400),LOC_CODE VARCHAR2(400));

2. Creating Object Table

CREATE TABLE EMP_DEPT_DETAILS OF  DEPT_DETAILS ;

Now table is ready for SELECT,INSERT,UPDATE and DELETE operation.

Inserting Data
INSERT INTO EMP_DEPT_DETAILS VALUES (10,'DNAME1','KOLKATA','KOL-001');
INSERT INTO EMP_DEPT_DETAILS VALUES (11,'DNAME2','BANGALORE','BAN-001');
INSERT INTO EMP_DEPT_DETAILS VALUES (12,'DNAME3','HYDERABAD','HYD-001');
INSERT INTO EMP_DEPT_DETAILS VALUES (13,'DNAME4','MUMBAI','MUM-001');

Updating Data
UPDATE EMP_DEPT_DETAILS SET DNAME ='HR' WHERE DEPTNO =10;

Deleting Data
DELETE FROM EMP_DEPT_DETAILS WHERE DEPTNO =10;

Selecting Data
SELECT * FROM EMP_DEPT_DETAILS ;
SELECT REF(A) FROM EMP_DEPT_DETAILS A WHERE LOC='BANGALORE';

Now see the use of REF

Create Table
CREATE TABLE EMPLOYEE_DETAILS (EMP_NAME VARCHAR2(50),EMPID NUMBER,DEPERT_DTS REF DEPT_DETAILS );

Insert Data into EMPLOYEE_DETAILS Table
INSERT INTO EMPLOYEE_DETAILS SELECT 'RABINDRA',2315,REF(A) FROM EMP_DEPT_DETAILS A WHERE LOC='BANGALORE';
INSERT INTO EMPLOYEE_DETAILS SELECT 'NANDAN',2316,REF(A) FROM EMP_DEPT_DETAILS A WHERE LOC='KOLKATA';

Select Data from EMPLOYEE_DETAILS Table
SELECT * FROM EMPLOYEE_DETAILS;
SELECT EMP_NAME,EMPID,DEREF(DEPERT_DTS) FROM EMPLOYEE_DETAILS;

REF Constraints

It will describe the relationship between a column of type REF and the object it references.

See The Example:

REF CONSTRAINT

CHECK and DEFAULT Constraint

Create table with Check Constraint

Create a Table with CHECK and DEFAULT Constraint

CREATE TABLE MyDept1  ( Deptno NUMBER(2)    CONSTRAINT MyDept_Deptno_PK1 PRIMARY KEY    CONSTRAINT MyDept_Deptno_CHK01 CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), DName VARCHAR2(16)  DEFAULT 'Not Given'  CONSTRAINT MyDept_DName_NN1 NOT NULL  CONSTRAINT MyDept_DName_UNQ UNIQUE  CONSTRAINT MyDept_DName_CHK01 CHECK(DName = UPPER(DName)), Loc VARCHAR2(14)  DEFAULT 'NOT GIVEN'  CONSTRAINT MyDept_Loc_NN1 NOT NULL  CONSTRAINT MyDept_Loc_CHK01 CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'NOT GIVEN','DALLAS')) );

Insert Data into MyDept1 Table
INSERT INTO MyDept1 VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO MyDept1 VALUES(1, 'ACCOUNTING', 'NEW YORK');

INSERT INTO MyDept
 *
ERROR at line 1: ORA-02290: check constraint (SCOTT.MYDEPT_DEPTNO_CHK01) violated.

This Record we are Inserting for DEFAULT Constraint
INSERT INTO MyDept1 (Deptno, DName) VALUES(20, 'OPERATIONS');

Now check your table data, while Inserting deptno=20 record we didn't provide the LOC value but while creating the table we set  DEFAULT value as 'NOT GIVEN'.

CHECK Constraint

It defines the conditions that each row must be satisfy.CHECK constraint is used to limit the value
range that can be placed in a column.


Restrictions:

• CHECK Constraint can refer to any column in same table but it can't refer any column from other table
• In CHECK Constraint we can't use pseudo columns.
• A Single column can have multiple CHECK Constraint.
• CHECK Constraint can be define column and table level.

DEFAULT option:

• The DEFAULT option is given to maintain a default value in column.
• The DEFAULT value can be leteral or any expression or sql function.

Syntax:

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name CHECK (column_name BETWEEN start_rng AND end_rng ) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name CHECK (column_name=UPPER(column_name)) DISABLE,
 COLUMN_NAMEn <DATA_TYPE>(WIDTH) DEFAULT SYSDATE);


See The Example:

CHECK CONSTRAINT EXAMPLE

FOREIGN KEY Constraint

• A FOREIGN KEY is a key used to establishes link between two tables (as parent child relationship).The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
• A FOREIGN KEY in one table that refers to the PRIMARY KEY in another table.
• Composite FOREIGN KEY should be declare in Table level.
• The FOREIGN KEY and the REFERENCED KEY can be same  table or view.

Restrictions :

• The FOREIGN KEY columns cannot be applied on LOB,LONG,VARRAY,NASTED TABLE,OBJECT AND TIMESTAMP WITH TIME ZONE.
• Composite FOREIGN KEY  cannot contain more than 32 columns.
• Child and parent tables must be in same data base.
• The referenced key(unique or primary) constraint on the parent table or view must already be defined. 
• REFERENCES Clause should be used when the FOREIGN KEY constraint is INLINE.

ON DELETE Clause :

If you want to delete the record from the parent and that parent having child then oracle will not allow you to delete the parent record. To delete the parent record we need to yes ON DELETE clause , by using ON DELETE clause oracle manage the referential integrity if you remove a referenced key .

• CASCADE:  If you want to delete dependent parent with child then use CASCADE
• SET NULL:    If you want to delete the only parent and child table dependent  FOREIGN KEY values set as NULL .

Syntax :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name REFERENCES <reference_table_name>[reference_table PK column_name],
 COLUMN_NAMEn <DATA_TYPE>(WIDTH));

See The Example:

FOREIGN KEY CONSTRAINT EXAMPLE

PRIMARY KEY Constraint Example

Type Of PRIMARY KEY Constraint

Type Of PRIMARY KEY Constraint	Example
Column Level PRIMARY KEY Constraint	CREATE TABLE SamplePK01(SampID NUMBER(2) CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY,SampName VARCHAR2(10),SampDate DATE);
Table Level PRIMARY KEY Constraint	CREATE TABLE SamplePK02(SampID NUMBER(2),SampName VARCHAR2(10),SampDate DATE,CONSTRAINT SamplePK02_SampID_PK PRIMARY KEY(SampID));
Composite PRIMARY KEY Constraint ,always declare in Table level	CREATE TABLE SamplePK05(SampID NUMBER(2), SampName VARCHAR2(10), SampDate DATE, CONSTRAINT SamplePK05_SampIDName_PK PRIMARY KEY(SampID, SampName) );

Now i am trying to insert data in  Column level PK Constraint table SamplePK01, PK is  SampID .

SQL> INSERT INTO SamplePK01 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK01 VALUES(1, 'SAMPLE02', SYSDATE);

INSERT INTO SamplePK01 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SAMPLEPK01_SAMPID_PK) violated

SQL> INSERT INTO SamplePK01 VALUES(NULL, 'SAMPLE02', SYSDATE);

VALUES(NULL, 'SAMPLE02', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK01"."SAMPID")

Insert data Into  Table level PK Constraint. Use above insert script by changing the table name.


Insert data Into Composite PRIMARY KEY  Constraint table SamplePK05, PK is SampID, SampName.

SQL> INSERT INTO SamplePK05 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK05 VALUES(1, 'SAMPLE02', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK05 VALUES(2, 'SAMPLE02', SYSDATE);

1 row created.

SQL> INSERT INTO SamplePK05 VALUES(NULL, 'SAMPLE03', SYSDATE);

VALUES(NULL, 'SAMPLE03', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPID")

SQL> INSERT INTO SamplePK05 VALUES(3, NULL, SYSDATE);

VALUES(3, NULL, SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPNAME")

FOREIGN KEY Constraint Example

Steps	Script
Create a table with PK constraint	CREATE TABLE SamplePK01(SampID NUMBER(2) CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY,SampName VARCHAR2(10),SampDate DATE);
Create a table with FK Constraint	CREATE TABLE SampleFK01(SampID NUMBER(2) CONSTRAINT SampleFK01_SampID_PK PRIMARY KEY, SampName VARCHAR2(10), SampDate DATE, SampIDFK NUMBER(2) CONSTRAINT SampleFK01_SampIDFK_FK REFERENCES SamplePK01(SampID));

Insert Below Records
INSERT INTO SampleFK01 VALUES(20, 'SAMPLE20', SYSDATE, NULL);
INSERT INTO SamplePK01 VALUES(10, 'SAMPLE10', SYSDATE);
INSERT INTO SamplePK01 VALUES(11, 'SAMPLE11', SYSDATE);
INSERT INTO SamplePK01 VALUES(12, 'SAMPLE12', SYSDATE);
INSERT INTO SamplePK01 VALUES(13, 'SAMPLE13', SYSDATE);
INSERT INTO SampleFK01 VALUES(21, 'SAMPLE21', SYSDATE, 10);
INSERT INTO SampleFK01 VALUES(22, 'SAMPLE22', SYSDATE, 10)
INSERT INTO SampleFK01 VALUES(23, 'SAMPLE23', SYSDATE, 12);

While Inserting below record we are getting error
INSERT INTO SampleFK01 VALUES(22, 'SAMPLE22', SYSDATE, 15);

INSERT INTO SampleFK01
*
ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - parent key not found

We are unable to inser the row because in parent table does not have any parent for this child (SampID=1).

 Now Check the data in parent and child table

 SELECT * FROM SamplePK01;
 SELECT * FROM SampleFK01;

Now we will try to delete the record from the parent and child table
DELETE FROM SamplePK01 WHERE SampID = 13;
DELETE FROM SamplePK01 WHERE SampID = 12;
DELETE FROM SamplePK01
ERROR at line 1:ORA-02292: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - child record found
We are deleting the record from parent table but this parent record have child record so it will not allow you to delete the parent record. For that first we need to delete from child table then delete from parent table or can we use ON DELETE CASCADE or we can use ON DELETE SET NULL

 

PRIMARY KEY Constraint

• A PRIMARY KEY is the candidate key that is used by the database designer for identifying an entity.
• There is no fiex rule for choosing the primary key, but generally it is the key that can be controlled by the user.
• Primary Key columns do not accept null values
• Primary Key columns do not accept duplicate values, that mean uniqueness of data.
• Oracle recommended Primary Key columns should be short and numeric.
• Primary Key constraint is a combination of NOT NULL+UNIQUE.

Restrictions :

• A table or view can have only one primary key.
• A composite primary key cannot have more than 32 columns.
• the same column or combination of columns cannot be declare as  primary key and a unique key.
• Primary key con't be implemented on columns having  LOB, LONG, VARRAY, NESTED TABLE, OBJECT, TIMESTAMP WITH TIME ZONE.

Syntax :

Column Level PRIMARY KEY Constraint :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH) CONSTRAINT Constraint_Name PRIMARY KEY,
 COLUMN_NAMEn <DATA_TYPE>(WIDTH));

Table Level PRIMARY KEY Constraint :

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH),
 COLUMN_NAMEn <DATA_TYPE>(WIDTH),

 CONSTRAINT Constraint_Name PRIMARY KEY(Column_name));

Composite PRIMARY KEY Constraint (always declare in Table level):

CREATE TABLE <TABLE_NAME>
(COLUMN_NAME1 <DATA_TYPE>(WIDTH) ,
 COLUMN_NAME2 <DATA_TYPE>(WIDTH),
 COLUMN_NAMEn <DATA_TYPE>(WIDTH),

 CONSTRAINT Constraint_Name PRIMARY KEY(Column_name1,Column_name2));


See The Example:

PRIMARY KEY CONSTRAINT EXAMPLE