Difference Between Foreign key and Primary key

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.

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.

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.

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