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