Difference between Primary Key and Foreign Key

Features of Primary Key

1. The primary key must contain a unique value for each row of the table.
    
2. The primary key cannot be NULL.
    
3. There can only be one primary key in a table.

Syntax

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ..
    ..
    ..
    ..
    ..
   ,
    PRIMARY KEY(column_in_the_table) 
);

Code

Let’s look into the code of the above example: 

CREATE TABLE Information(
  Aadhar_Number int NOT NULL,
  Person_Name varchar(255) NOT NULL,
  Age int (255),
  Address varchar(255),
  PRIMARY KEY (Aadhar_Number)
);
INSERT INTO Information
VALUES ("123456780987","RAM",25,"BENGALURU");
INSERT INTO Information
VALUES ("123456781234","RAM",15,"PUNE");
INSERT INTO Information
VALUES ("123456783456","ANMOL",26,"CHANDIGARH");
INSERT INTO Information
VALUES ("123098780987","KRISHNA",25,"BENGALURU");
SELECT * FROM Information;

Output

A Brief Introduction to Foreign Key

A foreign key is a column or columns that help in connecting two tables or in establishing a relationship between the tables. The foreign key of Table A is the primary key of Table B. The table consisting of the foreign key is called the child table, and the table consisting of the primary key is called the parent key.

Now let’s learn more about foreign keys with the help of an example.

There are two tables, “Customer Details” and “Pizza Details”. The primary key of the Customer Details table is the Customer_Id, and we can also take Phone_Number as the primary key. But in this case, we’ve taken Customer_Id as the primary key.

Customer Details

Pizza Details

Now in the table Pizza Details, there is Customer_Id, which is the primary key of the Customer_Details. Now this will act as the foreign key for Pizza_Details. Now, with the help of foreign keys, we can easily fetch data from the tables.

Now let’s look into the features of Foreign Key.

Features of Foreign Key

1. A foreign key is a key that is used to connect two tables.
    
2. The foreign key can have multiple NULL values.
    
3. There are two tables in the concept of a foreign key, the table with the foreign key is the child table, and the table with the primary key is the parent table.

Syntax

CREATE TABLE table_name( 
column1    datatype,
column2    datatype,  
FOREIGN KEY [column1, column2...] 
REFERENCES [ table name having primary key] (List of primary key) ...);

Code

CREATE TABLE Customer(
  Customer_Id int NOT NULL,
  Customer_Name varchar(255) NOT NULL,
  Phone_Number int (255),
  PRIMARY KEY (Customer_Id)
);
INSERT INTO Customer
VALUES (101,"ABC",9876543210);
INSERT INTO Customer
VALUES (102,"XYZ",9872345610);
INSERT INTO Customer
VALUES (103,"PQR",9987654330);


CREATE TABLE Pizza_Details (
  Pizza_Order_no int NOT NULL,
  Pizza_type varchar(255) NOT NULL,
  Customer_Id int,
  PRIMARY KEY (Pizza_Order_no),
  FOREIGN KEY (Customer_Id) REFERENCES Customer (Customer_Id)
);
INSERT INTO Pizza_Details
VALUES (1001,"Mexican",101);
INSERT INTO Pizza_Details
VALUES (1002,"Thin Crust",102);
INSERT INTO Pizza_Details
VALUES (1003,"Fully Loaded",103);
SELECT * FROM Customer;
SELECT * FROM Pizza_Details;

Output

Comparison Table between Primary and Foreign Key

Let's look at the comparison between the primary key and foreign key:

Frequently Asked Question

What is the major difference between primary key and foreign key?

The main difference between primary key and foreign keys is that the primary key defines the uniqueness of the data. In contrast, the foreign key is used to establish the relationship between the tables.

Can a primary key be a foreign key?

Yes, a primary key can be a foreign key if it connects two tables in the database. If Table A has a primary key and is also a foreign key for Table B, this makes the relationship between Table A and Table.

Can the primary key be NULL?

No, the primary key cannot be NULL because the primary key is used to identify the unique records in the table. But if the primary key's value is null, multiple rows can have unknown, unidentified values.

Why are keys used in DBMS?

Keys are used to identifying all the records uniquely in a table because multiple records may be the same. Keys are important in the database because they define the integrity of the data and help maintain its accuracy.

Why is a foreign key important?

The foreign key is important in RDBMS because it helps in connecting two tables or in establishing a relationship between the tables so that the user can access all the records from both tables.

Conclusion

In this article, we learnt about the Primary Key and Foreign key and the difference between Primary Key and Foreign key.

We hope this article helped you in your learning. If you want to learn more, refer to 

• Keys in DBMS
   
• What is Candidate Key in DBMS? 
   
• What Is Primary Key In SQL?
   

For more information, refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Python, Data Structures and Algorithms, Competitive Programming, System Design, and many more!

Head over to our practice platform Coding Ninjas Studio to practice top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!

Happy Learning Ninja!!