Primary and Foreign Key in SQL With Examples

In this tutorial, we will look at the concept of Primary and foreign key. Both the Primary and the foreign key are SQL constraints.

Constraints in SQL help us to manage the data and avoid any invalid transactions on it.

The primary key is limited to a single table and is put to uniquely identify the corresponding rows of a table.

When we talk about Foreign key, we can have as many Foreign keys as we want. A foreign key comes to use when we need to link tables to one another and have data spread over multiple tables.

Let us now dive into the concept of Primary and Foreign keys by understanding the definition, syntax, and examples of both keys.

What is a Primary Key in SQL?

A Primary key is a unique column we set in a table to easily identify and locate data in queries. A table can have only one primary key.

The primary key column has a unique value and doesn’t store repeating values. A Primary key can never take NULL values.

For example, in the case of a student when identification needs to be done in the class, the roll number of the student plays the role of Primary key.

Similarly, when we talk about employees in a company, the employee ID is functioning as the Primary key for identification.

Let us now understand the Syntax of creating the table with the Primary key specified.

Syntax:

CREATE TABLE tableName (
    col1 int NOT NULL,
    col2 varchar(50) NOT NULL,
    col3 int,
    …………….
    PRIMARY KEY (col1)
);

What is a Foreign key in SQL?

A Foreign key is beneficial when we connect two or more tables so that data from both can be put to use parallelly.

A foreign key is a field or collection of fields in a table that refers to the Primary key of the other table. It is responsible for managing the relationship between the tables.

The table which contains the foreign key is often called the child table, and the table whose primary key is being referred by the foreign key is called the Parent Table.

For example: When we talk about students and the courses they have enrolled in, now if we try to store all the data in a single table, the problem of redundancy arises.

To solve this table, we make two tables, one the student detail table and the other department table. In the student table, we store the details of students and the courses they have enrolled in.

And in the department table, we store all the details of the department. Here the courseId acts as the Primary key for the department table whereas it acts as the Foreign key in the student table.

Let us now look at the syntax of creating a table with a foreign key.

Syntax:

CREATE TABLE childTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    col3 int,
    ………...
    PRIMARY KEY (col1),
    FOREIGN KEY (col3) REFERENCES parentTable(parent_Primary_key)
);

Understanding Primary and Foreign Key in detail with Examples

1. Creation of Parent Table DataFlair

Query:

CREATE TABLE DataFlair(
emp_id varchar(5) NOT NULL,
name varchar(50),
location varchar(50),
experience int,
PRIMARY KEY(emp_id));

Output:

2. Creation of Child Table Location

Query:

CREATE TABLE location(
location_id varchar(5) NOT NULL,
location varchar(50) NOT NULL,
office_size int,
PRIMARY KEY(location_id),
FOREIGN KEY(location) REFERENCES dataflair(location));

Output:

 

3. Viewing the Parent and the Child Table

a. Parent Table or the DataFlair Table
Query: 

SELECT * FROM DataFlair;

Output:

b. Child Table of the Location Table:
Query:

SELECT * FROM Location;

Output:

4. Running Queries on the Parent and Child Table

Example 1: Let us now try to view the name of employee, location and the location id by using both the DataFlair and Location table.
Query:

SELECT emp_id , name, 
location.location_id,DataFlair.location 
FROM DataFlair RIGHT JOIN Location 
ON DataFlair.location=Location.location 
WHERE emp_id IS NOT NULL;

Output:

Example 2: Let us now view the locations where we have an office of DataFlair as of now.
Query:

SELECT DISTINCT(location.location_id) AS Location_ID,
DataFlair.location AS Office_Location
FROM DataFlair RIGHT JOIN Location 
ON DataFlair.location=Location.location 
WHERE emp_id IS NOT NULL;

Output:

How to Drop Primary and Foreign key

1. Drop Primary Key in SQL
Syntax:

ALTER TABLE tableName
DROP PRIMARY KEY;

Query:

ALTER TABLE dataflair
DROP PRIMARY KEY;

Output:

2. Drop Foreign Key in SQL
Syntax:

ALTER TABLE tableName
DROP FOREIGN KEY foreignKeyName;

Query:

ALTER TABLE location
DROP FOREIGN KEY location;

Output:

Difference Between Primary and Foreign Key

Sr.No	Primary Key	Foreign Key
1	Used to maintain the unique identification of data in the table.	Used to maintain the relationship between two or more relational tables.
2	Helps us to identify data in a database table.	Helps to identify the data in another table using the connection with the foreign key.
3	A table can have only one Primary Key.	A table can have any number of Foreign Keys.
4	The primary key is unique and Not Null.	A foreign key can contain duplicate values also.
5	Primary key can’t take Null as a value.	A foreign key can take NULL entries also.
6	Primary Key can’t be modified once entered.	A foreign key can be modified at any instance of time.
7	We can have Primary keys for temporary tables as well.	We can’t have Foreign keys for the temporary tables.
8	A Primary key can be defined on its own.	For defining a Foreign key, we need a parent table with a Primary Key.
9	Primary key creates clustered indexes on the table.	Foreign key does not create indexes on the table neither clustered nor unclustered.

Summary

In this tutorial, we have seen what a Primary and a Foreign Key is. We now know the definition of both and the syntax of Primary and foreign key.

We have seen examples of how to use both the Primary and Foreign key to create tables. And understanding the relationship they maintain with each other.

We have further enhanced our understanding by going through examples and understanding the working of queries on both the constraint keys.