Difference between Primary key vs Foreign key in table – SQL Tutorial Example

The main difference between the Primary key and the Foreign key in a table is that it’s the same column that behaves as the primary key in the parent table and as a foreign key in a child table. For example in the Customer and Order relationship, customer_id is the primary key in the Customer table but a foreign key in the Order table. By the way, what is a foreign key in a table and the difference between Primary and Foreign key are some of the popular SQL interview questions, much like truncate vs delete in SQL or difference between correlated and noncorrelated subquery? We have been learning key SQL concepts along with these frequently asked SQL questions and in this SQL tutorial, we will discuss what is a foreign key in SQL and the purpose of the foreign key in any table. 


By the way, this is the third article related to a primary key in SQL, the other being the difference between primary and unique key and How to find the second highest salary in SQL. If you are preparing for any technical job interview where you expect some SQL questions, check out these questions, they are worth preparing.

What is the Foreign key in a table in the Database?

A Foreign key is a column in one table which is the primary key on another table. A foreign key and a Primary key are used to define the relationship between two tables in a relational database. For example in Employee and Department relationship, we have two tables Department(dept_id, dept_name) and  Employee (emp_id, emp_name, dept_id). dept_id is the primary key in the Department table and the foreign key in the Employee table. 

Though it’s not required that name of a foreign key must be the same with primary key, we have kept it the same as per standard SQL best practices. Foreign key in a table enforce Referential Integrity constraint, which can be used to implement business rules e.g. referential integrity can stop you from creating an Employee with a non existent department. 

This kind of check maintains integrity of data in a relationship. As discussed in our post What is referential integrity in MySQL database, we have seen that it's implemented as foreign key constraint and can allow CASCADE UPDATE and DELETE. These referential action delete or update matching column in child table ( foreign key table) when corresponding row from parent table (primary key table ) is deleted or updated to maintain integrity of data.

Difference between Primary key and Foreign key in SQL

Here is some important difference between primary and foreign keys in a table which is worth remembering both on SQL interview point of view and knowledge point of view.

1) Name of foreign key can be different than the name of primary key it represent in other table. For example in our Employee and Department relationship, Primary key in Department table is dept_id  and we have used same name in Employee table to create foreign key. It could have been different e.g. departmentId or departmentID t etc.

2) Another difference between primary and foreign key is that unlike primary key, a foreign key can be null e.g. in our example you can have an Employee record for which dept_id can be null, this shows that no corresponding record in Department table.

3) One more difference between primary key and foreign key is that foreign key can be duplicate opposite to primary key which is always unique.

4) By using foreign key constraints, we can introduce referential integrity in multiple table relationship in SQL. Referential integrity guarantees data integrity, see benefits of Referential Integrity in SQL to know more.

5) Foreign key mostly works as a link between two table when we join tables using INNER JOIN and OUTER JOIN. For example, when we INNER JOIN both Employee with Department table, we can use dept_id as joining column. See How to join three tables in SQL for more details.

6) Table on which a column is declared as a primary key is known as parent table in the relationship and foreign key table is known as child table in a relationship. For example in Employee and Department relationship, Department is parent table because dept_id is primary key there and Employee is child table because dept_id is a foreign key in this table.

Primary key and Foreign key Example in SQL

One of the best examples to understand the Primary key and Foreign key in a table is Employee and Department relationship or the Customer and Order relationship. You can create an Order and Customer table in MySQL as following to create primary and foreign keys :

CREATE TABLE Customer (cust_id   INT NOT NULL,
                       cust_name VARCHAR(256),                      
                       PRIMARY KEY (cust_id)) ENGINE=INNODB;

CREATE TABLE ORDER (order_id INT NOT NULL,
                    amount INT NOT NULL,
                    cust_id INT,
                    FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
                    ON DELETE CASCADE) ENGINE=INNODB;

Now, cust_id is primary key in Customer table and the foreign key in Order table. If we try to insert an Order for which cust_id is something that is invalid in Customer table, MySQL database will reject such INSERT or UPDATE. This is one of the benefit of using Referential Integrity. 

It also allow to CASCADE UPDATE and DELETE operation which first delete or update a row in parent table e.g. Customer and then delete or update all matching rows in child table e.g. Order table.

That's all on what is a foreign key in a table and the difference between primary and foreign key in SQL. I suggest to create some table by yourself and try to test foreign key constraint by violating it and see how database e.g. Oracle, MySQL or SQL Server  behaves. To understand more try ON DELETE CASCADE and ON DELETE UPDATE to see how database maintains foreign key constraint. You can also see my post on Referential Integrity example on MySQL database

Other SQL Interview Questions for practice

What are ACID properties of any database transaction?

How to split string in stored procedure using PATINDEX and CHARINDEX function

How to copy or backup tables using SQL query

The difference between clustered vs non-clustered index in SQL.

How to create auto-incremented identity column in MySQL, Oracle, Sybase and SQL Server