Foreign Key

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

Check Constraint

 Check Constraint

Check Constraint : – Check constraint check the condition which is specify on the column that must be TRUE.


1) Check Constraint (Column Level) :-


a) With System defined name


SQL>create table student(regno number(10), name varchar2(10),dob date,course varchar2(10) check (course in ('MCA','BCA')));


b) With User defined name


SQL>create table student(regno number(10), name varchar2(10),dob date,course varchar2(10) constraint ck_course check (course in ('MCA','BCA')));


2) Check Constraint (Table Level) :-


a) With System defined name


SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), check (course in ('MCA','BCA')));


b) With User defined name


SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),constraint ck_course check (course in ('MCA','BCA')));


3) Composite Check Constraint :-Check constraint defined for more than one column is called Composite Check Constraint.


a) With System defined name


SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),check (course in ('MCA','BCA') and regno>1000));


b) With User defined name


SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), constraint ck_regno check (course in ('MCA','BCA') and regno>1000));

                  

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


                                                                                                   **THANK YOU**