SQL Foreign Key Constraint- Tutorial using Oracle

Create a student table

 
SQL> create table student(id number, name varchar2(10),address varchar2(10),
  2  gender varchar2(10), sid number);


Output
Table created.

Insert the following records

 
  SQL> insert into student values (&id,'&name','&address','&gender',&sid);
  Enter value for id: 421
  Enter value for name: satish
  Enter value for address: vellore
  Enter value for gender: male
  Enter value for sid: 111
  old   1: insert into student values (&id,'&name','&address','&gender',&sid)
  new   1: insert into student values (421,'satish','vellore','male',111)
  
  1 row created.
  
  SQL> /
  Enter value for id: 422
  Enter value for name: ram
  Enter value for address: mumbai
  Enter value for gender: male
  Enter value for sid: 111
  old   1: insert into student values (&id,'&name','&address','&gender',&sid)
  new   1: insert into student values (422,'ram','mumbai','male',111)
  
  1 row created.
  
  SQL> /
  Enter value for id: 423
  Enter value for name: jeff
  Enter value for address: pune
  Enter value for gender: male
  Enter value for sid: 111
  old   1: insert into student values (&id,'&name','&address','&gender',&sid)
  new   1: insert into student values (423,'jeff','pune','male',111)
  
  1 row created.
  
  SQL> /
  Enter value for id: 424
  Enter value for name: chris
  Enter value for address: us
  Enter value for gender: male
  Enter value for sid: 111
  old   1: insert into student values (&id,'&name','&address','&gender',&sid)
  new   1: insert into student values (424,'chris','us','male',111)
  
  1 row created.
  
  SQL> /
  Enter value for id: 425
  Enter value for name: harini
  Enter value for address: uk
  Enter value for gender: female
  Enter value for sid: 112
  old   1: insert into student values (&id,'&name','&address','&gender',&sid)
  new   1: insert into student values (425,'harini','uk','female',112)
  
  1 row created.

View the student table

 
SQL> select * from student;

Output
        ID NAME       ADDRESS    GENDER            SID
---------- ---------- ---------- ---------- ----------
       421 satish     vellore    male              111
       422 ram        mumbai     male              111
       423 jeff       pune       male              111
       424 chris      us         male              111
       425 harini     uk         female            112

Create the school table

 
SQL> create table school(sid number primary key, sname varchar2(10),slocation varchar2(10));


Output
Table created.

Insert Records in the School Table

 
  SQL> insert into school values (&sid,'&sname','&slocation');
  Enter value for sid: 111
  Enter value for sname: scope
  Enter value for slocation: sjt
  old   1: insert into school values (&sid,'&sname','&slocation')
  new   1: insert into school values (111,'scope','sjt')
  
  1 row created.
  
  SQL> /
  Enter value for sid: 112
  Enter value for sname: sense
  Enter value for slocation: tt
  old   1: insert into school values (&sid,'&sname','&slocation')
  new   1: insert into school values (112,'sense','tt')
  
  1 row created.

View the records in the School Table

 
SQL> select * from school;

Output
       SID SNAME      SLOCATION
---------- ---------- ----------
       111 scope      sjt
       112 sense      tt

Establish the sid column as the foreign key in the student table

 
SQL> alter table student add constraint student_sid_fr foreign key(sid) references school(sid);


Output
Table altered.

Establish foreign key as a table level constraint while table creation

 
SQL> create table student1(
    2  id number,
    3  name varchar2(10),
    4  sid number,
    5  constraint student1_sid_fr foreign key (sid) references school(sid));

Output
Table created.

Dropping a foreign key constraint

 
SQL> alter table student1 drop constraint student1_sid_fr;

Output
Table altered

Establishing foreign key at the column level when creating the table

 
SQL> create table student1(
  2  id number,
  3  name varchar2(10),
  4  sid number references school(sid));
  
Table created.
  
SQL> insert into student1 values (114,'satish',143);
insert into student1 values (114,'satish',143)
*
Output
ERROR at line 1:
ORA-02291: integrity constraint (SATISH.SYS_C0034226) violated - parent key not
found