Foreign Key

Foreign Key : – Foreign Key is a key in a table which is already defined as a primary/unique key in another/same table . Foreign Key create  Parent-Child relationship. It accept values of reference column from master table or null values

1) Foreign Key (Column Level) :-

a) With System defined name

SQL>create table courses (course_id number primary key,course_name varchar2(20));

SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number

                     references courses(course_id));

b) With User defined name

SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));

SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number constraint fk_course_id references courses(course_id));

2) Foreign Key (Table Level) :-

a) With System defined name

SQL>create table courses (course_id number primary key,course_name varchar2(20));

SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number, foreign key (course_id) references courses(course_id));

b) With User defined name

SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));

SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number, constraint fk_course_id foreign key(course_id) references courses(course_id));

3) Composite Foreign Key :-

a) With System defined name

SQL>create table courses (course_id number,course_name varchar2(20),primary key (course_id,course_name));

SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number, foreign key (course_id) references courses(course_id));

b) With User defined name

SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));

SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number, constraint fk_course_id foreign key(course_id) references courses(course_id));

4) On delete set null :-

 

a) With System defined name

SQL>create table courses (course_id number primary key,course_name varchar2(20));

SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number,foreign key (course_id) references courses(course_id) on delete set null);

                  

b) With User defined name

SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));

SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number, constraint fk_course_id foreign key(course_id) references courses(course_id) on delete set null);

 5) On delete cascade :-

 

a) With System defined name

SQL>create table courses (course_id number primary key,course_name varchar2(20));

SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number, foreign key (course_id) references courses(course_id) on delete cascade);

                  

b) With User defined name

SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));

SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number,constraint fk_course_id foreign key(course_id) references courses(course_id) on delete cascade);

--------------------------HAPPY LEARNING -----------------------------

                                                                                                   **THANK YOU**