Primary Key VS Foreign Key-MySQL

Key in MySQL is the fundamental elements for constructing a relationship between two tables. They are very useful in the maintenance of a relational database structure.

First, let us see what is a Primary Key-

A column cannot have NULL values.

The primary key can be any field or column of a table, which should be a unique and non-null value for each record or row.

The primary key is a unique or non-null key that uniquely identifies every record in a table or relation. Each database needs a unique identifier for every row of a table, and the primary key plays a vital role in identifying rows in the table uniquely. The primary key column can't store duplicate values. It is also called a minimal super key; therefore, we cannot specify more than one primary key in any relationship.

For example, we have a table named books with attributes such as ID, Name, and Category. Only the ID column can never contain duplicate and NULL values because each book has a unique identification number. This feature helps to identify each record in the database uniquely. Hence, we can make the ID attribute a primary key.

The query for creating such a table and creating the ID column as a primary key column will be like this:

CREATE TABLE books (
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_category VARCHAR(255),
PRIMARY KEY (book_id)
);

Now let's see what is foreign key?

The Foreign key is a field that contains the primary key of some other table to establish a connection between each other.

A foreign key always matches the primary key column in another table. It means a foreign key column in one table refers to the primary key column of another table. A foreign key is beneficial in relational database normalization, especially when we need to access records from other tables.

A foreign key creates a parent-child relationship with the tables where the parent table holds the initial column values, and the child table references the parent column values. We can achieve this relationship only when the foreign key constraint is found on the child table.

In the primary key section, we have created a table for the books. Now, let’s suppose, we have another table of authors in our database that includes the author’s ID as a primary key, author’s first name, and last name.

Then we want to create a foreign key to the author’s ID in the books table. So, to create a foreign key on author_id while creating the books table, we run this query:

CREATE TABLE books (
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_category VARCHAR(255),
author_id INT,
PRIMARY KEY (book_id),
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Conclusion:

A primary key is used to ensure data in the specific column is unique and It is used to identify each record into the database table uniquely.

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

The main difference between them is that the primary key identifies each record in the table, whereas the foreign key is used to link two tables together.

Thank You for reading!
Do comment if you liked the blog.
Have a Good Day : )