Primary Key and Foreign Key with example
Primary keys and Foreign keys define the relationship between tables in a relational database system. In this blog, we will learn the concepts of Primary Key and Foreign Key with an example.
What is a Primary Key?
Every table in a database must have a Primary key. The primary key consists of one or more columns in a table and is used to uniquely identify each row. An index is automatically created on a Primary key of a table. A primary key column/group of columns must satisfy the following conditions:
- Not Null: No values in the column(s) should be Null.
- Unique: The column or combination of columns must be unique.
- Not more than one: There cannot be more than one Primary key in a table.
In the Employees table shown below, Employee_ID is the primary key because it uniquely identifies each row. There cannot be two employees having the same Employee_ID. Their names and other values can be the same, but they can still be differentiated based on the primary key.
What is a Foreign Key?
A foreign key is a column or set of columns that refers to the primary key column/s in another table. It has the same value as the Primary key in the referenced table. Two tables are set to be in a ‘relation’ based on the Primary and Foreign keys between the tables. A foreign key has the following features:
- Nulls allowed: Foreign key column can accept Null values.
- Duplicate values are allowed.
- More than one foreign key: A table can have more than one foreign key referring to more than one Primary key in various tables.
Example: Primary key and foreign key relationship
In the above tables, Department_ID is the Primary key in Department table and it is a foreign key in the Employee table. The two tables are related using these keys and can be joined to retrieve additional information from these tables. Primary key in Employee table is Employee_ID.
For e.g. To get the department name of Employee ID 100, you can join the Employee table with the Department table and get the Department name .