Primary Key and Foreign Key in MySQL Explained with Examples

MySQL is the most widely used open source relational database management system in the world. MySQL is used by many web applications out there. While you are reading this article, you are actually indirectly using MySQL database.

If you are completely new to relational database management systems, then I would recommend reading the below article to get an idea about it.

Read: What is relational Database Management Systems

The primary responsibility of a database is to store information about different objects or entities, and their relationship with each other. For example, an educational institution might store details about students, courses provided. There can be a relationship between courses and students. This relationship can then be called as enrollment.

If you are completely new to databases, then consider a database table as an excel sheet, with different columns and multiple rows. Columns will contain different attributes that will describe a row in that table.

Let’s take the example of students and courses table. Attributes(columns in the table) will help us distinguish one entity from another (one row from the other - A row in the students table will have all the details about a particular student, and a row in the courses table will describe course details).

When we say students table, the table should have columns like Firstname, Last Name, Phone Number, Email Address etc. We could consider the name attribute (column) to help distinguish one student from the other(ie: Uniquely identify one particular student). However that does not sound like a good solution. Because two different students(rows) could have identical names.

To be able to identify one student uniquely, we need to have an attribute(column), that is guaranteed to be unique for each and every student entry (row) in the database table. This attribute (column) is called as primary key in a database.

We can also consider phone number column in the students table to identify one particular student uniquely, or else we can consider email address as well.

The selection of the primary key (attribute, or column) to identify one entry/entity (row) uniquely should depend on how confident you are that it will be non-empty, and will be unique for each individual entity(row) in the column.

The primary key should never be NULL. For example, if a particular student does not provide email address or phone number, we cannot use either of them as our primary key.

In order make this selection of primary key's more easier, we can actually create/add an artificial attribute (column) from our side, that will be defined as unique. This artificial key(column) created by us can then be used as a primary key. Mostly this artificial column will contain numbers that will auto increment itself for each entry(row) added to the table. For example, the first row will have an entry of 1 in the artificial column that we created, and the second row will have 2, and the third will have 3 and so on. Hence we can be hundred percent sure that it will be unique and non-empty.

Above shown is our "students" table with 5 different students. There are 4 columns in our example. From these columns, s_id column is our artificial column to be used as primary key. The values in that column are automatically created for each individual students that are part of the table. As the entries are auto-incremented for each entry, it will be hundred percent unique for each student. We can use s_id column as our primary key to identify one particular student uniquely from the table.

Let's achieve the same example in a mysql database. Am going to now connect to a test MySQL server, and run several commands, to achieve the same result we saw above.

mysql> create database university;
Query OK, 1 row affected (0.00 sec)

In the above show MySQL command, we are creating a database named "university". Now let's create a students table inside that database.

mysql> use university;
Database changed

mysql> create table students (s_id INT(10) NOT NULL AUTO_INCREMENT, s_firstname VARCHAR(30) NOT NULL, s_lastname VARCHAR(30) NOT NULL, s_email VARCHAR(40), PRIMARY KEY (s_id));
Query OK, 0 rows affected (0.07 sec)

Let's now add several entries to our newly created students table.

mysql> insert into students (s_firstname,s_lastname,s_email) values ('Shankar', 'Bhat', 'shankar@example.com');
Query OK, 1 row affected (0.02 sec)

mysql> insert into students (s_firstname,s_lastname,s_email) values ('Venkat', 'Rao', 'venkat@example.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into students (s_firstname,s_lastname,s_email) values ('Mohan', 'Nair', 'mohan@example.com');
Query OK, 1 row affected (0.06 sec)

mysql> insert into students (s_firstname,s_lastname,s_email) values ('Abhijeet', 'Patel', 'abhi@example.com');
Query OK, 1 row affected (0.03 sec)

mysql> select * from students;
+------+-------------+------------+---------------------+
| s_id | s_firstname | s_lastname | s_email             |
+------+-------------+------------+---------------------+
|    1 | Shankar     | Bhat       | shankar@example.com |
|    2 | Venkat      | Rao        | venkat@example.com  |
|    3 | Mohan       | Nair       | mohan@example.com   |
|    4 | Abhijeet    | Patel      | abhi@example.com    |
+------+-------------+------------+---------------------+

The autogenerated artificial column that we added as primary key in our example is also called as surrogate keys.

Now we can use the s_id column in the table(ie: our primary key), to uniquely identify one particular student as shown below.

mysql> select * from students where s_id='3';
+------+-------------+------------+-------------------+
| s_id | s_firstname | s_lastname | s_email           |
+------+-------------+------------+-------------------+
|    3 | Mohan       | Nair       | mohan@example.com |
+------+-------------+------------+-------------------+
1 row in set (0.00 sec)

We can also create a primary key by using multiple columns from the table. Let's add another column to the same example(let's add a column to the same table that stores the phone number of the student.)

As we already discussed, primary key should be able to uniquely identify one particular record(row) in the table. Its perfectly possible that two students can have the same first name, two students can have the same last name, two students can also have the same phone number(well if both of them are from the same house).

But its less likely that two students will have the same phone number and the same firstname. So the firstname and the phone number, combined together will uniquely identify a particular student for sure. So in this case we can construct a primary key without using a surrogate key(the artificial auto incremented column that we added previously).

Let's now create the university database with the above students table.

mysql> create database university;
Query OK, 1 row affected (0.00 sec)

Let's now create our students table with two columns as primary keys(ie: combination of s_firstname & s_phone). Its also called as composite key.

mysql> create table students (s_firstname VARCHAR(30) NOT NULL, s_lastname VARCHAR(30) NOT NULL, s_email VARCHAR(40), s_phone BIGINT(10) NOT NULL, PRIMARY KEY (s_phone, s_firstname));
Query OK, 0 rows affected (0.09 sec)

Let's now add some entries(well the same exact entries mentioned in the table above.)

mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Shankar', 'Bhat', 'shankar@example.com', '7303075409');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Venkat', 'Rao', 'venkat@example.com', '7404076894');
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Mohan', 'Nair', 'mohan@example.com', '7404076892');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Abhijeet', 'Patel', 'abhi@example.com', '7404076991');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Manoj', 'Nair', 'manoj@example.com', '7404076892');
Query OK, 1 row affected, 1 warning (0.06 sec)

Now we have our full table ready with a composite primary key(the full table entry is shown below.)

mysql> select * from students;
+-------------+------------+---------------------+------------+
| s_firstname | s_lastname | s_email             | s_phone    |
+-------------+------------+---------------------+------------+
| Shankar     | Bhat       | shankar@example.com | 7303075409 |
| Manoj       | Nair       | manoj@example.com   | 7404076892 |
| Mohan       | Nair       | mohan@example.com   | 7404076892 |
| Venkat      | Rao        | venkat@example.com  | 7404076894 |
| Abhijeet    | Patel      | abhi@example.com    | 7404076991 |
+-------------+------------+---------------------+------------+

We can now uniquely identify a student using both phone number and firstname as shown below.

mysql> select * from students where s_firstname='Mohan' AND s_phone='7404076892';
+-------------+------------+-------------------+------------+
| s_firstname | s_lastname | s_email           | s_phone    |
+-------------+------------+-------------------+------------+
| Mohan       | Nair       | mohan@example.com | 7404076892 |
+-------------+------------+-------------------+------------+
1 row in set (0.00 sec)

Also the database wont let you create another entry with same firstname and the same phone number. Running the below query should throw a primary key error.

mysql> insert into students (s_firstname,s_lastname,s_email,s_phone) values ('Manoj', 'Pillai', 'manoj@example.com', '7404076892');
ERROR 1062 (23000): Duplicate entry '7404076892-Manoj' for key 'PRIMARY'

In the above shown entry, both the firstname and the phone number is already there in the table, due to which it is throwing the "Duplicate entry for key 'PRIMARY'" error.

You can actually add a primary key on an existing table using ALTER TABLE statement in MySQL as shown below.

mysql> ALTER TABLE students ADD PRIMARY KEY (s_id);

You can also add a composite primary key using the below command on an existing table(both these examples uses students table, and the columns that we have available in students table. You will have to replace this with corresponding tablename and columns in your case.)

mysql> ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (s_phone,s_firstname);

What is Foreign key in SQL ?

In simple words "foreign key is something using which you can link two different tables together". "It is a column in one table, that is linked to the primary key of another table".

Let's take the same students table that we created while learning about primary keys(not the students table we created for composite primary keys, but the students table we created with autogenerated artificial column called s_id.). The students table is shown below.

mysql> select * from students;
+------+-------------+------------+---------------------+
| s_id | s_firstname | s_lastname | s_email             |
+------+-------------+------------+---------------------+
|    1 | Shankar     | Bhat       | shankar@example.com |
|    2 | Venkat      | Rao        | venkat@example.com  |
|    3 | Mohan       | Nair       | mohan@example.com   |
|    4 | Abhijeet    | Patel      | abhi@example.com    |
+------+-------------+------------+---------------------+
4 rows in set (0.00 sec)

Let's now create a new table named "courses" in the same university database. This table will have the list of courses that the university is offering to students.

mysql> create table courses (c_id INT(10) NOT NULL AUTO_INCREMENT, c_name VARCHAR(30) NOT NULL, PRIMARY KEY (c_id));
Query OK, 0 rows affected (0.07 sec)

Let's add few courses to our newly created courses table using MySQL INSERT statement as shown below.

mysql> insert into courses (c_name) values ('Computer Science');
Query OK, 1 row affected (0.05 sec)

mysql> insert into courses (c_name) values ('Economics');
Query OK, 1 row affected (0.03 sec)

mysql> insert into courses (c_name) values ('Arts');
Query OK, 1 row affected (0.07 sec)

mysql> insert into courses (c_name) values ('Chemistry');
Query OK, 1 row affected (0.03 sec)

mysql> insert into courses (c_name) values ('Astro Physics');
Query OK, 1 row affected (0.05 sec)

Finally our courses table looks like the below.

mysql> select * from courses;
+------+------------------+
| c_id | c_name           |
+------+------------------+
|    1 | Computer Science |
|    2 | Economics        |
|    3 | Arts             |
|    4 | Chemistry        |
|    5 | Astro Physics    |
+------+------------------+
5 rows in set (0.00 sec)

Let's now create another table named enrollment. This table will contain student enrollment details.

Enrollment table will be making links to students table, as well as courses table. This is because an enrollment is nothing but the combination of students and courses table. A student opts for a particular course in the university, and this creates an entry in the enrollment table.

mysql> create table enrollment (e_id INT(10) NOT NULL AUTO_INCREMENT, e_StudentID integer, e_CourseID integer, e_year YEAR, PRIMARY KEY (e_id), FOREIGN KEY (e_StudentID) REFERENCES students(s_id), FOREIGN KEY (e_CourseID) REFERENCES courses(c_id));
Query OK, 0 rows affected (0.10 sec)

In the above shown MySQL query, we are creating an enrollment table with 4 attributes(columns). First is the e_id(the artificial column that is autogenerated, to uniquely identify one particular enrollment.). The second column is the e_StudentID column which actually references the s_id column from students table(because s_id column in students table will uniquely identify one particular student.). Similarly the third column e_CourseID references c_id column from courses table(c_id column in courses table is the primary key which will uniqely identify one particular course). The last column e_year simply records the year in which a particular student got enrolled for a course. 

In the columns e_CourseID and e_StudentID, you can only have entries that are valid and present in c_id and s_id columns of courses and students table. This foreign relationships to primary key s_id and c_id will keep the integrity of the data inside enrollment table.

Let's now add few entries inside our enrollment table as shown below.

mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (1, 3, 2016);
Query OK, 1 row affected (0.13 sec)
mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (2, 4, 2016);
Query OK, 1 row affected (0.04 sec)
mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (3, 2, 2016);
Query OK, 1 row affected (0.01 sec)
mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (4, 1, 2016);
Query OK, 1 row affected (0.04 sec)

Pay close attention to the entries we inserted inside enrollment table. The values we provided for e_StudentID and e_CourseID are valid entries available inside s_id and c_id tables of students and courses table. Adding an entry inside enrollment table, with values that are not present in primary key of students and courses table will create an error as shown below.

mysql> insert into enrollment (e_StudentID, e_CourseID, e_year) values (5, 1, 2016);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`university`.`enrollment`, CONSTRAINT `enrollment_ibfk_1` FOREIGN KEY (`e_StudentID`) REFERENCES `students` (`s_id`))

This error is because the value of 5 provided for e_StudentID is not a valid one. Its not present in the students table(see the students table shown previously..It only has 4 entries for 4 students. Hence only 4 valid students are available for us to use in enrollment table.)

Our enrollment table with all valid entries we created looks like the below.

mysql> select * from enrollment;
+------+-------------+------------+--------+
| e_id | e_StudentID | e_CourseID | e_year |
+------+-------------+------------+--------+
|    1 |           1 |          3 |   2016 |
|    3 |           2 |          4 |   2016 |
|    4 |           3 |          2 |   2016 |
|    5 |           4 |          1 |   2016 |
+------+-------------+------------+--------+
4 rows in set (0.00 sec)

The values that you see in e_StudentID and e_CourseID are primary keys from students and courses table.

Foreign keys can be used to protect the integrity of your table. It will also keep the links between tables in perfect and valid state.