EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Database Management Tutorial Primary Key and Foreign Key
Secondary Sidebar
OLTP vs OLAP

Clustering Methods

Clustering Algorithms

Pie Chart in R

Data Science vs Data Engineering

What is MapReduce in Hadoop?

Primary Key and Foreign Key

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

PL SQL Training (4 Courses, 2+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more