Home » Oracle » Oracle Sql » How to use foreign key in oracle

How to use foreign key in oracle

What is Foreign key

Foreign key in Oracle is a way to relate multiple Tables. It is a cross-linking between the tables.

  • A foreign key is a column or set of column which refers to the primary key or Unique key in the same table or another table
  • Foreign key values are based on data values and they are purely logical construct not physical pointers
  • Foreign key value must match a primary key value or unique key value or it is null.

Foreign key constraints are called Referential integrity constraints. The referenced table is called the parent table while the table with the foreign key is called the child table.

how to use foreign key

Let’s check with the example of EMP and DEPT.

SQL>CREATE TABLE "DEPT"
 ( "DEPTNO" NUMBER(2,0),
 "DNAME" VARCHAR2(14),
 "LOC" VARCHAR2(13),
 CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
 )
 SQL>CREATE TABLE "EMP"
 ( "EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2),
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0),
 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
 );
 SQL> desc emp
 Name Null? Type
 
 EMPNO NOT NULL NUMBER(4)
 ENAME VARCHAR2(10)
 JOB VARCHAR2(9)
 MGR NUMBER(4)
 HIREDATE DATE
 SAL NUMBER(7,2)
 COMM NUMBER(7,2)
 DEPTNO NUMBER(2)
 SQL>
 SQL> desc dept
 Name Null? Type
 
 DEPTNO NOT NULL NUMBER(2)
 DNAME VARCHAR2(14)
 LOC VARCHAR2(13)
 SQL>
 insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
 insert into dept values(20, 'RESEARCH', 'DALLAS');
 insert into dept values(30, 'RESEARCH', 'DELHI');
 insert into dept values(40, 'RESEARCH', 'MUMBAI');
 insert into emp values( 7698, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
 insert into emp values( 7782, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
 insert into emp values( 7788, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
 insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
 insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
 insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
 SQL> select  from emp;
 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
 
 7698 BLAKE MANAGER 7839 01-MAY-07 2850 10
 7782 CLARK MANAGER 7839 09-JUN-08 2450 10
 7788 SCOTT ANALYST 7566 09-JUN-12 3000 20
 7789 TPM ANALYST 7566 09-JUN-17 3000
 7790 TOM ANALYST 7567 09-JUL-17 4000
 7560 T1OM ANALYST 7567 09-JUL-17 4000 20

EMP table contains the DEPT_NO column. and DEPT Table contains the DEPT_NO column also and it is the primary key on the table.

See also  How FNDFS (Oracle Report Review agent) works in EBS environment

Now we don’t want any entries in the table EMP where DEPT_NO does not match with DEPT_NO in the DEPT column as we cannot have an emp whose dept Number does not exist. Let’s see if we can do this with the current Setup

SQL> insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 50);
 1 row created.

But this succeeded and the structure has caused a data integrity issue

To avoid these types of data issues, we can enforce the Foreign key constraints on the EMP table.
Let’s see again

drop table emp;
 drop table dept;
 SQL>CREATE TABLE "DEPT"
 ( "DEPTNO" NUMBER(2,0),
 "DNAME" VARCHAR2(14),
 "LOC" VARCHAR2(13),
 CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
 )
 SQL>CREATE TABLE "EMP"
 ( "EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
 "JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2),
 "COMM" NUMBER(7,2),
 "DEPTNO" NUMBER(2,0),
 CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
 CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
 REFERENCES "DEPT" ("DEPTNO") ENABLE
 );
 SQL> desc emp
 Name Null? Type
 
 EMPNO NOT NULL NUMBER(4)
 ENAME VARCHAR2(10)
 JOB VARCHAR2(9)
 MGR NUMBER(4)
 HIREDATE DATE
 SAL NUMBER(7,2)
 COMM NUMBER(7,2)
 DEPTNO NUMBER(2)
 SQL>
 SQL> desc dept
 Name Null? Type
 DEPTNO NOT NULL NUMBER(2)
 DNAME VARCHAR2(14)
 LOC VARCHAR2(13)
 SQL>
 insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
 insert into dept values(20, 'RESEARCH', 'DALLAS');
 insert into dept values(30, 'RESEARCH', 'DELHI');
 insert into dept values(40, 'RESEARCH', 'MUMBAI');
 insert into emp values( 7698, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
 insert into emp values( 7782, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
 insert into emp values( 7788, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
 insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
 insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
 insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );

Now let’s try to enter the same row

SQL> insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 50);
 insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 50)
 *
 ERROR at line 1:
 ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key
 not found

So it has avoided the entries of the bad data.

See also  SQL to check Workflow Agent Listeners are running

The same is the scenario with Delete from the DEPT table. We should not be deleting the rows of the dept where emp has some records. Without foreign key constraints, it will happen and will cause bad data. But with Foreign key, this will be avoided

SQL>  delete from dept where deptno=10;
  delete from dept where deptno=10
 *
 ERROR at line 1:
 ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

Foreign Key Clauses on Delete Option

CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
 REFERENCES "DEPT" ("DEPTNO") ENABLE
 ON DELETE [CASCADE |SET NULL]

Case 1: Foreign key defined without ON DELETE option
You will not be able to delete records from the parent table if records are found in the child table

Case -2 Foreign key defined with ON DELETE SET NULL option
Let see how it works

SQL> alter table emp add CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ON DELETE SET NULL;
 Table altered.
 SQL> select * from emp where empno=7698;
   EMPNO     DEPTNO
  -------  ----      
   7698      10
 SQL>  delete from dept where deptno=10;
 1 row deleted.
 SQL> commit;
 Commit complete.
 SQL> select * from emp where empno=7698;
   EMPNO     DEPTNO
  -------  ----      
   7698 

So on deleting the rows from the parent table, child rows foreign key column is made null

Case -3 Foreign key defined with ON DELETE CASCADE option

SQL> alter table emp add CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ON DELETE cascade;
 Table altered.
 SQL> delete from dept where deptno=10;
 1 row deleted.
 SQL> commit;
 Commit complete.
 SQL> select * from emp where  deptno=10; ;
 no rows selected
 SQL>

So on deleting the rows from the parent table, child rows are also deleted

See also  Adop(Ad online patching utility) explained R12.2

Alter Table Foreign key

We can create a Foreign key in Oracle after the Table creation also

 alter table emp add CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ; 

How To drop the Foreign key Constraint

SQL> alter table emp drop constraint "FK_DEPTNO";
 Table altered.

How to disable the constraint

SQL> alter table emp  disable   constraint "FK_DEPTNO";
 Table altered.

How to enable the constraint

SQL>  alter table emp   enable  constraint "FK_DEPTNO";
 Table altered.
 SQL>

Also Reads
Check constraint in Oracle
Not Null constraint in Oracle
How to Add primary key in oracle : primary key uniquely identify the row in the table. How to Add primary key in oracle, how to drop primary key, how to create a composite key
drop foreign key constraint oracle
unique key in oracle : Unique key enforces unique in the column in the table and helps us identify the row quickly. Oracle creates the unique index for the key if no index is available
delete query in oracle
https://en.wikipedia.org/wiki/Foreign_key

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top