Primary Key and Foreign Key Examples

In this tutorial, we are going to see what does mean Primary Key and Foreign Key in SQL and also some examples of them.
 

Primary Key

A primary key is a field that identifies a row in a table. Identify means that there is only one row that is identified by the key, the primary key is unique.

The primary key is not mandatory, but it’s important. If you want to update a row or delete it, you need a way to identify precisely the row to be processed. If a table doesn’t have a primary key, you can’t guarantee that, so you risk corrupting data or altering data unintentionally.
 

 
Additionally, the primary key plays a very important role in the performance of the database, because behind each primary key is an index.
 
Example of Primary Key:

CREATE TABLE Persons (
    PersonID int AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Age int,
    Address VARCHAR(100)
);

The column PersonID is a primary key of “Persons” table. This means that the values in PersonID column uniquely identify the rows in the table. The following statement display all the data in “Persons” table.

> SELECT * FROM persons;

+----------+-----------+--------+-------------------------------+
| PersonID |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|    101   |    Alex   |   25   | 819 Saint Francis Way         |
|    102   |   Emily   |   15   | 171 Jarvisville Road Michigan |
|    103   |   Jean    |   35   | 188 Clay Street Indiana       |
|    104   |    Bob    |   40   | 285 Java Lane Missouri        |
+----------+-----------+--------+-------------------------------+

 

 

Foreign Key

A foreign key is a simple mechanism to ensure referential integrity between data in different tables. In other words, the foreign key forces a table to be linked to the data of another table. In the following example, “Orders” table is linked to “Persons” table by PersonID.
 
Example of Foreign Key:
Let’s assume that each person has made orders. To store the orders, you can create a new table named “Orders”:

CREATE TABLE Orders (
    OrderID int AUTO_INCREMENT PRIMARY KEY,
    NumOrder int NOT NULL,
    PersonID int,
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

The column “PersonID” is a foreign key that refers to the column “PersonID” in the table “Persons”. We used the “Foreign Key” constraint to establish this relationship.
 

The following statement display all the data in “Orders” table.

> SELECT * FROM orders;

+----------+-----------+----------+
|  OrderID |  NumOrder | PersonID |
+----------+-----------+----------+
|    55    |   00001   |   101    |
|    56    |   00002   |   101    |
|    57    |   00003   |   102    |
|    58    |   00004   |   104    |
+----------+-----------+----------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More