Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Foreign key and Primary key

Difference Between Foreign key and Primary key

May 1, 2011 Posted by Roshan Ragel

The key difference between foreign key and primary key is that foreign key is a column or a set of columns that refer to a primary key or a candidate key of another table while primary key is a column or a set of columns that can be used to uniquely identify a row in a table.

A column or a set of columns that can be used to identify or access a row or a set of rows in a database is called a key. A primary key in a relational database is a combination of columns in a table that uniquely identify a row of the table. Foreign key in a relational database is a field in a table that matches the primary key of another table. The foreign key is used to cross reference tables.

Difference Between Foreign key and Primary key - Comparison Summary

CONTENTS

1. Overview and Key Difference
2. What is Foreign key
3. What is Primary key
4. Side by Side Comparison – Foreign key and Primary key in Tabular Form
5. Summary

What is Foreign key?

Foreign key is a referential constraint between two tables. It identifies a column or a set of columns in one table, called the referencing table that refers to a set of columns in another table, called the referenced table. The foreign key or the columns in the referencing table must be the primary key or a candidate key (a key that can be used as the primary key) in the referenced table. Moreover, foreign keys allow linking data across several tables. Therefore, the foreign key cannot contain values that do not appear in the table that it refers to. Then the reference provided by the foreign key helps to link information in several tables and this would become essential with normalized databases. Multiple rows in the referencing table may refer to a single row in the referenced table.

Difference Between Foreign key and Primary key

Figure 01: Foreign Key Mapping

In ANSI SQL standard, the FOREIGN KEY constraint defines foreign keys.  Furthermore, it is possible to define the foreign keys when creating the table itself.  A table can have multiple foreign keys, and they can reference different tables.

What is Primary key?

Primary key is a column or a combination of columns that uniquely defines a row in a table of a relational database. A table can have at most one primary key. Primary key enforces the implicit NOT NULL constraint. Therefore, a column with primary key cannot have NULL values in it. Primary key can be a normal attribute in the table that is guaranteed to be unique such as a social security number, or it could be a unique value generated by the database management system such as a Globally Unique Identifier (GUID) in Microsoft SQL Server.

Key Difference Between Foreign key and Primary key

Figure 02: Primary Key

Furthermore, the PRIMARY KEY constraint in ANSI SQL Standard defines primary keys.  It is also possible to define a primary key when creating the table. In addition to that, SQL allows primary key to be made up of one or more columns, and each column that is included in the primary key is implicitly defined to be NOT NULL. But some database management systems require making the primary key columns explicitly NOT NULL.

What is the Difference Between Foreign key and Primary key?

 Foreign key vs Primary key

Foreign key is a column or group of columns in a relational database table that provides an association between data in two tables. Primary key is a special relational database table column or a combination of multiple columns that allows to uniquely identify all table records.
NULL
Foreign key accepts NULL value. Primary key value cannot be NULL.
Number of Keys
Table can have multiple foreign keys. Table can have only one primary key.
  Duplication
Tuples can have a duplicate value for a foreign key attribute. Two tuples in a relation cannot have duplicate values for a primary key attribute.

Summary – Foreign key vs Primary key

The difference between foreign key and primary key is that foreign key is a column or a set of columns that refer to a primary key or a candidate key of another table while primary key is a column or a set of columns that can be used to uniquely identify a row in a table.

Reference:

1.“Foreign Key.” Wikipedia, Wikimedia Foundation, 12 May 2018. Available here  
2.“Primary Key.” Wikipedia, Wikimedia Foundation, 3 Apr. 2018. Available here 

Image Courtesy:

1.’Foreign-Key-Mapping’By MovGP0 – Own work, (CC BY-SA 3.0) via Commons Wikimedia  
2.’Erd-id-as-primary-key’By UniCollab – Own work, (CC BY-SA 3.0) via Commons Wikimedia  

Related posts:

Difference Between Primary key and Unique key Difference Between SQL Server 2008 and Express Difference Between RDBMS and ORDBMS Difference Between DBMS and Data Warehouse Difference Between Explicit Cursor and Implicit Cursor

Filed Under: Database Tagged With: primary key, Primary key and Foreign key, Primary key vs Foreign key

About the Author: Roshan Ragel

Dr.Roshan G. Ragel, is a Doctorate in Computer Science and Engineering and Member of IET (UK) and IEEE. His research interests include Micro-architectural aspects of Embedded Systems Design and their Security and Reliability issues.

Comments

  1. emelly says

    April 27, 2017 at 1:51 pm

    well explained document ,thank you

    Reply

Leave a Reply Cancel reply

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

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Close and Near

Difference Between Close and Near

Difference Between Malic Acid and Citric Acid

Difference Between Malic Acid and Citric Acid

Difference Between Straw and Hay

Difference Between Hexagonal Close Packing and Cubic Close Packing

Difference Between Hexagonal Close Packing and Cubic Close Packing

Difference Between Polymers and Metals

Difference Between Polymers and Metals

Latest Posts

  • What is the Difference Between Lactam and Lactim
  • What is the Difference Between Carbamazepine and Oxcarbazepine
  • What is the Difference Between HTLV 1 and 2
  • What is the Difference Between Bohr Effect and Haldane Effect
  • What is the Difference Between Oxybenzone and Avobenzone
  • What is the Difference Between Flakiness Index and Elongation Index
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.