Before understanding the concept of foreign keys, let’s understand what a key is. So, as we know, in a database, we store our data in the form of tables, in the form of rows and columns in a table. What if we want to connect two or more tables with each other? For that, we need to understand the concept of a key. So, using the concept of primary key and foreign key Let's understand what the foreign key is in this article.
A foreign key is a column or a group of columns in a database table which must match with some other column in another table. In simple words, we can say a column which is common in two tables can be considered a foreign key. Let’s understand more clearly what foreign keys are using an example given below.
This is a student table, in which we have roll no, name, class, and age as attributes. This type of storage of data is seen in our databases (in the form of tables). Let's explore another table named "Student Info Table."
As we can see, it is a table containing information about a student, such as fees, status, contact information, and address. If you see, both tables (student and student info) have a common column, which is Roll No. So rolling no column in the student info table will work as a foreign key and rolling no column in the student table will work as a primary key. Using the concept of a foreign key and a primary key, we can connect both the tables.
Foreign Key Query in MySQL
Let’s see how we can write code of foreign key in MySQL.
CREATE TABLE Student info (
Roll no int NOT NULL,
Fees status varchar (30),
Contact no int NOT NULL,
Address varchar (50) NOT NULL,
PRIMARY KEY (Roll no),
FOREIGN KEY (Roll no) REFERENCES Student (Roll no)
);
Let’s explore how we can add content to these tables.
INSERT INTO Student VALUE (21,’Ramesh’, ‘10th’,15);
INSERT INTO Student VALUE (22,’Atul’, ‘3rd’,8);
INSERT INTO Student VALUE (23,’Rahul’, ‘8th’,13);
INSERT INTO Student VALUE (24,’Piyush’, ‘12th’,17);
INSERT INTO Student VALUE (25,’Arnav’, ‘7th’,13);
Remove the Foreign Key Constraint
If we want to write a SQL command to drop a foreign key constraint, then we will write the following command.
ALTER TABLE Student
DROP FOREIGN KEY
Why do we need Foreign Key?
Using the concept of a foreign key, we can connect two or more tables as we can create a relationship between those tables with a common attribute.
Foreign keys also help us to maintain the referential integrity of the database.
How does Foreign Key maintain Referential Integrity?
Let's understand this concept with the help of two tables? One is the student table, which contains the roll number, name, and address of a student, and the other table is the course table, which contains information about a student's course.
So, roll no in the course table (referencing table) will work as a foreign key and roll no in the student table will work as a primary key. Here, the student table is the reference table and the course table is the referencing table. So, the main thing we'll learn in this topic is what happens if we insert, update, or delete something in this table. So, let's explore that part also.
Insert – If we insert any entry into our base table, that is, the student table, then it will not create any issue. We can insert as many entries as we want, and it won’t create any issue for insertion. As a result, there is no violation in insertion.
Delete – If we delete any entry from our base table, like if any student leaves an organization, then we have to delete that data from our table. Let’s consider that roll no. 1 leaves the organization, and we delete that entry from the student table. But in the course table, we see that roll no. 1 is currently studying network subjects. So directly deleting will create an issue. Integrity will be lost in this case. But if we consider a case where we insert a new entry, let’s say roll no 4, and then we delete that roll no, then it will not create any issue. So, deletion may create a violation.
So, what do you think will be the solution to this issue? So, we have a concept of a delete cascade where the row that we are deleting will be automatically deleted from other tables also. Another method is to delete set null where the row we are deleting is found, and if the same row is found in another table, then we set the foreign key value null on that set. Another method is to delete data with no action. As the name suggests, it will not do any action, so data will be deleted in this case.
- Update – If we want to update any row, then it will create an issue, like if we have to update that data in other related tables, as we saw in the deletion. We can use the same method that we perform on deletion, that is, on update cascade, on update set null, on update no action. Updating may result in a violation.
To learn more about the concepts of primary key and the distinction between primary key and foreign key on Scaler Topics
Author: Arnav Bhardwaj
Top comments (0)