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:
alter table Table Add Constraint Constraint_Name FOREIGN KEY(common_attribute) REFERENCES Primary_Key_TABLE (Common_attribute);
- Partner is the Table name.
- Partner_fk is the Constraint Name.
- 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);