FOREIGN KEY IN SQL
Foreign key concept is very importamt concept in SQL. This article explains creation,addition and deletion of foreign key.
We all have an identity of ourselves that distinguishes us from others. People generally identify us using our names. We usually identify two persons with their full names when they have similar first names. Hence, we find out ways to identify humans uniquely. This process of unique identification extends to every other type of object. In order to uniquely identify something, we should have knowledge about that particular thing. This concept is similar to that of the primary and foreign keys. Let us dive deeper into the concepts of FOREIGN KEY IN SQL using a real-life application example.
CONTENT
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
DESCRIPTION
Let’s understand the concept of the foreign key using examples and diagrams. Suppose two tables of records are maintained for your class; one is Details, and the second is named Scores. Details table stores the basic information about every student in your class, like the roll number, names, city, phone number, and address. The table looks like this –
The scores table stores the marks scored by every student in a year in two semesters, like the roll, total marks, and semester. The table looks like this –
Now you have got two tables with different sets of information in each. If you want to find out every detail, like the name, city, phone number, address, marks, and semester of a particular student then you have to connect both the tables, as every needed information is not available in a single table. So, there must be some way to create a link between both tables.
We know about the primary keys – the columns with unique values for each record. To create a link between the tables, we have to use this primary key field from the first table and connect it with the second table. For this reason, we have to declare a column as a foreign key in the second table, also known as a referencing key. The concept is like a lock and key. You can have several keys for a single lock, but you will not have multiple locks for a single key. This lock is analogous to the primary key, and the key corresponds to the foreign key.
Getting back to our example of tables, the roll number from the Details table will be our primary key, and the roll column from the Scores table is our foreign key. Thus the tables will get connected in this way –
To clarify more using data, suppose there are ten students in your class. It doesn’t matter to us if any two students have common names, as we will identify each student uniquely by their roll number. Thus, in the details table, a roll number is the primary key that cannot repeat. Now the marks scored by each student in both semesters are recorded in the scores table. For every student, we will get two entries as each student will have the marks for both the first and the second semesters.
In order to connect the tables, we will declare the column of roll from the scores table as the foreign key. This column is then used to join the tables. You must have guessed that the values in the foreign key column will repeat, that is, every roll number will appear twice for two-semester scores. Repetition is allowed in the foreign key column. This seems justified as foreign keys are the constraints for developing a connection between the tables, and you would not like to miss out on any data. Thus, if we want to find out the name and marks scored by the student with roll number 7, we can connect or join both tables and get all the relevant data.
Read Later
Also explore: Database and SQL Online Courses & Certifications
NEED OF FOREIGN KEY
The correct way of working with data is to create multiple tables with related data. It is done to avoid confusion and mess, and primarily to reduce the computation time. Considering our example, if you store all the data of the Details and the Scores table in a single table, you will end up with lots of redundant data. It will not only consume a considerable amount of memory but also will cause data repetition. It will lead to an increase in the cost of storage and computation time while querying.
There is no need to store the data for columns city, address, and phone number every time you store the marks for students. Hence it is better to store the related data separately in two tables and then connect both the tables using the primary and foreign keys. It is the usual thing done worldwide with millions of data. Arranging the data properly in separate tables and connecting the tables in the right way causes the creation of a data model, which is highly essential for maintaining a huge pool of data.
WHAT IS FOREIGN KEY?
A foreign key is a column declared in a table for creating a relationship with other tables. It is a type of constraint defined on the columns of a table to build a proper relation between the tables, which is essential for joining and preventing any actions that may destroy the data. Some points to remember about foreign keys are –
- Foreign keys are not unique, that is, we can have repetitive values in the foreign key column.
- We can null values in the foreign key. This null value will be for those records which do not have the primary key in the first table. It is a rare case and is generally not observed.
- Two or more columns can together make up the foreign key column, like two or more columns can create the primary key field.
- There can be multiple foreign keys in a single table.
Thus, the foreign key defined in the second table, is used for connecting with the first table having the related primary key. Now, let’s see how to create foreign keys.
CREATE A FOREIGN KEY
Foreign key column can be declared while creating the table. The syntax for creating the foreign key is –
CREATE TABLE table_name (
Column_name column_type,
Column_name column_type,
….
FOREIGN KEY (Column_name) REFERENCES first_table_name(column_name)
)
Thus, we create the connection between the tables by using the keyword FOREIGN KEY, then using the keyword REFERENCES followed by the table name and the column name with which we want to connect.
Considering our example of student tables, let’s create both the tables and define the foreign key –
CREATE TABLE Details (
Roll_number INT(3),
Name VARCHAR(60),
City VARCHAR(60),
Phone_Number INT,
Address VARCHAR(100),
PRIMARY KEY (Roll_Number)
);
CREATE TABLE Scores (
Roll INT(3),
Marks INT,
Semester INT(1),
FOREIGN KEY (Roll) REFERENCES Details (Roll_Number));
From the above, it is clear that we have created both the tables by defining the primary key in table Details and the foreign key in table Scores. The column of Roll from Scores references from or relates to the column of Roll_Number from the Details table. This will thus create the connection between the two tables, and after that we can fetch any number of columns from both tables together by joining the tables concerning the keys, that is the columns of roll from Scores and roll_number from Details.
ADD A FOREIGN KEY
Now, suppose you have created the tables of Details and Scores, without defining the foreign key. That is you have done something like this –
CREATE TABLE Scores (
Roll INT(3),
Marks INT,
Semester INT(1));
Hence you are not having any means to connect the tables. You need to have a foreign key defined in your Scores in order to connect or join both the tables. How can you do that now? Well you can use the alter table statement for adding foreign key in your table. This will be like –
ALTER TABLE Scores ADD FOREIGN KEY (Roll) REFRENCES Details(Roll_number);
Thus, we are altering the table of Scores in which we are adding the constraint of foreign key on column roll which will reference to the column of roll number from the Details table. Now you can join both the tables and get what you want. Thus the syntax is –
ALTER TABLE second_table_name
ADD FOREIGN KEY (column_name_of_second_table) REFRENCES first_table_name(column_name_of_first_table);
DELETE A FOREIGN KEY
The syntax for deleting the foreign key is –
ALTER TABLE table_name DROP FOREIGN KEY column_name
Considering our example, if we want to remove the foreign key constraint from the Roll column of Scores table, then we need to do –
ALTER TABLE Scores DROP FOREIGN KEY Roll
SUMMARY
Creating foreign keys is essential and is essential in joining multiple tables. We can create a foreign key while declaring the table or may also add a foreign key to an already existing table. We can drop the foreign key constraint from the table when we no longer need it. The foreign key constraint is defined on a column that references the primary key column of some other tables. There may be more than one foreign key column referencing another table. The declaration remains the same for multiple foreign keys. Data Models are generated by connecting different tables together. This connection is done using the primary and foreign keys.
CONTRIBUTED BY-Megha Chadha
This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio
Top Picks & New Arrivals
