Primary and Foreign Key in SQL With Examples

FREE Online Courses: Enroll Now, Thank us Later!

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:

Create query in SQL

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:

 

Foreign Key in SQL

3. Viewing the Parent and the Child Table

a. Parent Table or the DataFlair Table
Query: 

SELECT * FROM DataFlair;

Output:

View Table in SQL

b. Child Table of the Location Table:
Query:

SELECT * FROM Location;

Output:

view table in SQL

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:

Running queries in SQL

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:

SQL Queries Example

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:

drop primary key in SQL

2. Drop Foreign Key in SQL
Syntax:

ALTER TABLE tableName
DROP FOREIGN KEY foreignKeyName;

Query:

ALTER TABLE location
DROP FOREIGN KEY location;

Output:

drop foreign key in SQL

Difference Between Primary and Foreign Key

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

Your opinion matters
Please write your valuable feedback about DataFlair on Google

follow dataflair on YouTube

5 Responses

  1. VIVEK S says:

    It was nice

  2. Ashwini says:

    Good explanation

    • DataFlair Team says:

      Hello Ashwini,

      Glad that you liked our article. Share this article on social media to help others also.

  3. Ankit Jadeja says:

    For the parent table there is one mistake, Primary should be location not an emp_id.

Leave a Reply

Your email address will not be published. Required fields are marked *