Add Foreign Key in Oracle Alter Table

Before stepping into this, you need to get a clear vision of what foreign key is. Click here to explore foreign keys. 

In the previous articles, we have discussed how we can add a primary key and drop a primary key to an existing Table. Now, the question arises:

How to Add Foreign Key in Existing Table in SQL ?

Several times it happens, that we have explored different kinds of data that are interlinked. So, we always use to attach that kind of data through a Foreign key. For an instance, a table is present in the database that contains information about the different students. Now, an examination is declared that needs a couple of students to attend the same. That means every student will be allotted a partner for that. We can make another table named Partner and can link the two tables with common Roll_No. 

Follow the Stu table as below:

Now the Partner table can be as follows:

In the above table, you can see, that a person with 180  as a Roll_no has Sunita as a partner. So, we got to know here, that the Roll_No column of the Stu table can act as a Primary key for Roll_No as a foreign key in the Partner table. 

To add a Foreign key in the already existing table follow the syntax below:

Syntax

alter table Table
Add Constraint Constraint_Name 
FOREIGN KEY(common_attribute) REFERENCES Primary_Key_TABLE (Common_attribute);

1. Partner is the Table name.
2. Partner_fk is the Constraint Name.
3. Roll_No is the common column in both tables.

Oracle Add Foreign Key in Existing Table Example

alter table partner
Add Constraint Partner_fk 
FOREIGN KEY(Roll_No) REFERENCES Stu(Roll_No);

Output