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.
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.
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
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