Primary Key and Foreign Key

Introduction to Primary Key

The following article provides an outline for Primary Key and Foreign Key. A primary key is a non-null or unique key which identifies each record in a table or relation. Every row of a table in a database requires a unique identifier, and the primary key plays a critical role in uniquely identifying rows in the table. Duplicate values cannot be stored in the primary key column. Because we can’t provide more than one primary key in any relationship, it’s also known as a minimal super key. It is also used to uniquely identify every record in the database table. The value of the primary key column can never be NULL.

There can only be one primary key in a table. Because the primary key is a unique attribute, it cannot be used to hold duplicate values in relation. The primary key is automatically indexed because it is a clustered index by default. The table’s primary key value cannot be changed. If the user wants to delete it, make sure the referenced foreign key doesn’t have its value in it. Regardless of whether the values are contained in a foreign key or not, we can insert them into the primary key column without restriction. The primary key cannot have a parent-child connection/relation in the table. It can be defined on the temporary tables.

For example, there is a customer database with attributes like Name, ID, and City. Because each client has a unique identification number, the ID field can never include duplicate or NULL values. This feature supports the unique identification of each database record. As a result, the ID attribute can be used as a primary key.

What is Foreign Key?

A foreign key is a set of one or more columns in a database that uniquely identifies another database record in another table to ensure referential integrity. It is known as the reference key, and it’s responsible for establishing a link between two different tables in a database. A foreign key column in one table must always match the main key column in another. It means that one table’s foreign key column relates to another table’s primary key column. In relational database normalisation, a foreign key is useful, specifically when there is a need to access records from several other tables. A foreign key establishes a parent-child connection between tables, with the parent table storing the original column values and the child table referencing them. Only when the foreign key restriction is found on the child table can we achieve this relationship.

The foreign key is implemented to connect two tables. It means that one table’s foreign key relates to another table’s primary key. It accepts a NULL value. A table can contain one or more foreign keys. The foreign key column allows users to store duplicate values. It’s not a clustered index by default. The user has to create clustered index manually. The value of the foreign key can be deleted from the table without affecting the primary key of some other table. A value that does not exist in a primary key’s column cannot be added to the referenced foreign key. A foreign key constraint cannot be defined on the temporary tables. Foreign keys can create a parent-child relationship.

For example, there is a database with attributes like ID, student_Info, as well as course. The student_Id column can be made a foreign key here.

ID	student_Id	student_Info	course
NULL	1	jon	maths
NULL	4	merry	science
NULL	3	smith	history
NULL	2	alex	history
NULL	6	rajesh	science
NULL	5	emma	maths

We may define the foreign key in the contact table as follows to erase the referential data that eliminates records from both tables:

Code:

FOREIGN KEY (student_Id) REFERENCES student(ID)
ON DELETE CASCADE
ON UPDATE CASCADE

When user delete a record from the student table, the relevant rows in the table are likewise deleted, and both tables are automatically updated.

Here is another example of an employee and sales table.

EMPLOYEE:

EMP_ID	EMP_NAME	EMP_PHONE	EMP_AGE	SALES_ID
E01	Ram	9654342321	26	S04
E02	Shyam	9154342322	31	S05
E03	Pooja	9456342323	42	S01
E04	Meena	9654342301	24	S02
E05	Kajal	9874342453	35	S03
E06	Rakesh	9754342326	31	S06

SALES:

SALES_ID	EMP_ID	SALES_NO
S01	E03	1
S02	E04	2
S03	E05	3
S04	E01	4

In the table, EMP_NAME, as well as EMP_ID both, are candidate keys for relation EMPLOYEE table but SALES_ID is the primary key. Only one out of many employee keys.

Conclusion – Primary Key and Foreign Key

In this article, We’ve come to the conclusion that both keys are important in relational database schemas because they build relationships between many tables. The value in the primary key column is always unique for each record in the table, however, the value in the foreign key column can be replicated. The primary key uniquely identifies a record in a table or relation, whereas the secondary key identifies a record in a table or relation. In addition, a foreign key connects two tables. So, in this article, we have seen what is the primary key and foreign key along with examples.

Recommended Articles

We hope that this EDUCBA information on “Primary Key and Foreign Key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL Primary Key
2. Primary Key in SQL
3. Cassandra Partition Key
4. Primary Memory