Difference Between Primary Key and Foreign Key

What are Keys in SQL?

For any database, we all know that it stores data in rows and columns. But let us say you want to find a unique row in a DB. If you want to connect two tables, how would you do such tasks? Well, you guessed it right, here keys come into the picture. There are various types of keys that a database contains. Each key has its own properties and use cases.

What is Primary Key?

A Primary Key is a special column or set of columns in a relational database table that provides a unique identifier for every row. It ensures that there are no duplicate values in that particular column or set of columns and that no part of the primary key can have a null value. The primary key plays a crucial role in ensuring the integrity and consistency of the data in the table.

Let’s say you have a table named student, which stores information related to students.

Student Table

The student table will have the following columns.

It might be possible that two or more students have the same names. But, as we know, each student has a unique roll no. To fetch the phone no of a particular student who has roll no 2, we will go to the database and query it to return the information of roll no 2.

Here we can see that every person has a roll no. that is unique. And we can directly identify a person based on his roll no. So primary key is a unique value used to identify a row in a table. The primary key can be anything in the table which is unique for a row. Here we can also take the phone no as the primary key because it is unique for every person.

Syntax for Creation of Primary Key

Let's see how we can create a primary key by taking an example of the Student table.

Here we created a table named Student with a primary key set as roll no. The table would look something like this.

Syntax for Dropping Primary key

If you want to drop the primary key of the student table, you will write like this.

Things to Note about Primary Key

• A table can have only one primary key.
• The primary key is often denoted as Pk.
• You can make the primary key by combining two or more columns. The resultant key is known as a composite key. Example: Phone_no + name can also act as primary key because it can uniquely identify rows.
• Primary key cannot be null.

What is Foreign Key?

A foreign key is a column or group of columns that connects or makes a relationship between two tables. Let's understand foreign key with an example. Suppose you want to store the performance of students about whom we were talking in the primary key discussion. We created a performance table that would store information on the performance of students

Performance Table

The schema of this table looks as follows:

The performance has a column named roll_no which is the same as the primary key of the student table. This column will act as a foreign key for the performance table. Now we can find the performance of any student by querying the performance table and specifying the student roll_no in the query.

Syntax for Creation of Foreign Key

Let’s take an example of Performance table.

The above query creates a performance table, that references roll_no, which is the primary key of the student table and will act as a foreign key here. PID is set as the primary key of the performance table.

Syntax for Dropping Foreign key

The following is the syntax of the query used to drop a foreign key from a table.

If you want to drop the foreign key of the performance table, you would write like this:

Things to Note in Foreign Key

• Foreign key is denoted by Fk.
• Table with a foreign key is called a child table or referencing table, and the table with the primary key is called a parent table or referenced table.
• Foreign keys can have multiple null values.

Difference Between Primary key and Foreign key - Comparison Chart

What is the Use of Primary Key?

1. Primary key is used to find unique records in the table.
2. You can use a primary key when you want to store non-null values for a particular column, and it should be unique, for example, a roll no of a student Student table.
3. You don't want duplicate rows.

What is the Use of Foreign Key?

1. Use a foreign key when you want to connect two tables or maintain a relationship between them.
2. Foreign Keys help to maintain referential integrity.

Referential Integrity states that keys that reference elements of other tables need to be connected to those other fields. If there is a change, everything gets updated together, not separately. Let's understand referential integrity with the help of an example.

Here we have two tables, Employee and Department, which contain the information of Employees and Department, respectively.

Employee

Department

Here department no in the Employee table is a foreign key. As you can see, there are only two departments in the department table. If we try to add a new employee with department no 027, the concept of referential integrity comes into the picture here. Since 027 is not a valid foreign key, hence it will prevent new employees with the wrong department, no to be added.