Diagram maker for developers

In relational tables within a database, there are several types of ‘keys’. Keys are used as identifiers for the information in the table, as well as defining the relationships between tables. Let’s take a closer look at two of these identifiers: primary keys and foreign keys.

Make your own ER diagram in Gleek.

What is a primary key?

Primary keys are columns in a table that uniquely identify the information in rows or tuples. Each table has just one primary key that can have several attributes, and are used to identify information within the table. Without a primary key, finding information in the table would be extremely difficult, if not impossible.

Learn more on the primary key in our recent Unique key vs. Primary key comparison article.

Additionally, in some database designs, a surrogate key, which is an artificially generated unique identifier, may be used alongside or instead of natural primary keys to ensure data integrity and facilitate efficient database operations.

Read on the Key principles of effective database design.

When to use a primary key

Without primary keys, specifying individual records wouldn’t be possible. We see examples of primary keys in real life, which are known in the software world as natural primary keys. Some examples of natural primary keys – things used to identify people or things specifically – are ID numbers, addresses, and vehicle identification numbers. Each of these keys can identify only one item.

Primary keys are used most often in what are called ‘parent tables’ – tables which are referenced by other tables.

What is a foreign key?

A foreign key is one or more columns in a table that references the primary key of another, creating a link between them. Foreign keys cannot exist without being linked to a primary key. Unlike primary keys, tables can have more than one foreign key.

Let’s say that there are two tables, and the parent table is called STUDENTS, with the primary key being Student Names. Another table, called COURSES, contains what courses are available, and what students are in the courses. Student Names in the COURSES table is a foreign key, which refers back to the STUDENTS table.

When to use foreign keys

Foreign keys act like primary keys in that they are also used to identify specific entries in tuples, but foreign keys always reference another table. Tables with foreign keys are called ‘child tables’, because they always link back to a table with a primary KEY.

The differences between primary keys and foreign keys

As stated before, there can only be one primary key per table, but there can be more than one foreign key, and foreign keys can reference more than one table. Foreign keys can have a NULL value, while primary keys cannot. Another notable difference is the ability to delete keys. If a primary key is deleted from the parent table, it will cause a malfunction, but foreign keys can easily be removed from child tables.

Make your own ER diagram in Gleek.