MySQL
POSTGRESQL
SQL
SQL SERVER

Primary Key vs. Foreign Key: A Complete Comparison

intro

Primary key vs. Foreign key: What are they, how do they work, and which one should you choose for your database operations? Learn here.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Ahh... Keys. First and foremost, keys lock and unlock doors—doors that safeguard the entrance to many precious buildings. Keys are used so often that many of us forget that they’ve locked doors in the first place!

In the database world, however, things are a little different. Here, “keys” mostly refer to indexes; indexes that enhance SELECT query performance. In the database world, primary key vs. foreign key is a frequent point of discussion for many developers and DBAs alike: what are these keys? How do they differ and what kind of key should I use to enhance my database performance? Read on.

What Are Keys in a Database?

The first question we need an answer to is what we mean by saying “keys” in the first place. Everything’s simple. Keys denote just another term for indexes in our database. Various database management systems offer various keys available for us to use.

In this blog, we’re using MySQL Server and its counterparts MariaDB Server and Percona Server as examples, but other database management systems also offer the following variants:

  • B-Tree indexes — These are the most “ordinary” type of index you can imagine. Such indexes use a B-tree table structure to store and organize data.
  • Unique indexes — These kinds of indexes uniquely sort our data (i.e., get rid of duplicate rows within a column in our table.)
  • Spatial indexes — These kinds of indexes are used for geometry-type columns that are defined as NOT NULL.
  • Clustered indexes — These are indexes in which the order of the rows in the data pages are ordered in the exact same way as the order of the rows in the index.
  • Hash indexes — These indexes are a specific kind of index that helps direct lookups, but they work only in memory (i.e., the MEMORY storage engine in MySQL.)
  • Covering indexes — These kinds of indexes “cover” all fields required for our SQL queries to successfully execute; queries that fully use the covering index read the index instead of reading the data itself, thus making our SQL query significantly faster.

Enough about index types, though; you probably know them all by heart. Let’s get into the main question—what is a primary key? What is a foreign key? What makes a primary key vs. foreign key comparison possible?

SQL Primary Keys

Let’s dig into the primary key vs. foreign key comparison. You’ve surely heard these two terms before, right?

In many cases, most of you would’ve heard of the term “primary key” when defining a column to increment automatically and that’s kind of the concept of primary keys in the first place. In any database management system, a primary key is a unique identifier for rows in a table.

Usually, columns with a primary key can only consist of integer values and that’s for a good reason—primary keys increment column values by 1 each time a new row is added and that means that they solve a couple of issues in one go:

  1. We no longer need to define all the columns we’re inserting data into—we can skip the column with the primary key on it instead (most of such columns are called “id” or similar.)
  2. In some cases, we no longer have to keep track of the last record inserted into a table — as all rows now have a unique ID that increments automatically, the last ID represents the amount of rows if no rows have been deleted.
  3. The process is 100% accurate and automatic, even if there is data inside a table already. How convenient is that?

In other words, primary keys are SQL indexes that uniquely identify rows—primary keys will usually consist of unique, automatically incrementing integers and will never ever have a NULL value alongside them.

SQL Foreign Keys

In simple terms, foreign keys refer to a singular column or several columns that refer to a PRIMARY KEY in another table. Keys referring to other keys are a big part of the part of the title itself — “Foreign” means “not a frequent guest.” And, indeed, foreign keys aren’t frequent guests in our database either.

Foreign keys are mostly used to enhance relationships between tables—such indexes are indexes that are linked to a column in another table. As such, a rule is being established—a value can only be added or updated in a column X if the same value exists in a column Y in another table. What a strange guest, right?

Primary Key vs. Foreign Key in SQL

Now, towards the real discussion—What’s the main primary key. vs a foreign key differences? Is there any particular difference to begin the comparison with?

Indeed, attentive readers will realize that there are a couple:

  • The existence of a column with a foreign key on it means that that column refers to a PRIMARY KEY in another table.
  • Primary keys are unique identifiers for a column—all values in that column will be unique and never repeat.
  • A foreign key column points to a column in a column in another table. That column will have unique values and can have a primary key on itself, too.
  • If a foreign key exists on a column, that column must contain a value from a column in a table it's “connected” to.

In short, the core difference between a primary key vs. foreign key is that a primary key is used to uniquely identify rows and is almost exclusively used on integer columns, while a foreign key is used to “connect” rows in two tables. When a foreign key is in use, rows existing in a table X can only be added or updated if the relevant column in a table Y holds the same value.

Keys and SQL Clients

Now that you know what the primary key vs. foreign key difference is, it’s time to explore the opportunities posed by these SQL index types.

What’s a better way to practice and explore opportunities than using an SQL client like the one developed by DbVisualizer? Grab our free trial and enjoy a free version of DbVisualizer.

DbVisualizer is used by many popular companies from Tesla and Volkswagen to NASA. These companies use DbVisualizer because of a couple of reasons:

  • DbVisualizer makes visualizing the data inside of your database instance, no matter what kind of DBMS you find yourself using quick, comfortable, easy, and straightforward.
  • DbVisualizer has everything you need to manage your database instances from a SQL client to the ability to create monitors to repeatedly query your databases in set intervals.

Here’s one of the features of DbVisualizer—SQL DDL query viewer:

Viewing the DDL structure of a table in DbVisualizer
Viewing the DDL structure of a table in DbVisualizer

DbVisualizer can surprise you with many things, too, but we’ll keep that a secret. Joking—review all of our features, the Pro version of DbVisualizer is free for 21 days, so get your free trial now, and we’ll see you in the next blog.

Summary

The primary key vs. foreign key comparison is simple—both primary keys and foreign keys are indexes with a distinct difference: a primary key is used to uniquely identify rows and is almost exclusively used on integer columns with automatically incrementing values (see example above), while a foreign key is used to “connect” rows in two tables.

Frequently Asked Questions

What is a primary key in SQL?

A primary key is a type of index that is used to uniquely identify rows in a column and is almost exclusively used on integer columns.

What is a foreign key?

A foreign key is a type of index that is linked to a column in another table. That allows such indexes to “connect” with rows in another table.

Should I use primary keys or foreign Keys?

Primary keys should be used whenever you need values in a column to increment automatically—foreign keys should be used whenever you need values existing in table X to only be updated if the value in table X’s foreign key column exists in the indexed column in table Y.

Where can I learn more about databases?

Check the Database Dive YouTube channel out—they make videos on database performance, availability, and security often. Free of charge!

Where can I grab a free trial for DbVisualizer?

Click here. The free trial is valid for 21 days. The link is no secret — explore the possibilities offered by a primary key vs. foreign key comparison, then make sure to share it with your colleagues!

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide To the SQL DECLARE Statement

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SERVER 7 min 2024-12-12
title

SQL Upsert: Inserting a Record If It Does Not Exist

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-12-10
title

A Complete Guide to the MySQL COUNT Function

author Antonello Zanini tags MySQL 9 min 2024-12-09
title

How to Find Elements In an Array: PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL 3 min 2024-12-05
title

MySQL Nullable Columns: Everything You Need to Know

author Antonello Zanini tags MySQL 6 min 2024-12-03
title

A Complete Guide to the MySQL GROUP_CONCAT Function

author Antonello Zanini tags Data aggregation MySQL 8 min 2024-12-02
title

How and Why to Add an Index in SQL

author Lukas Vileikis tags MySQL POSTGRESQL Search SQL SQL SERVER 8 min 2024-11-28
title

MySQL 9.0 and the New EXPLAIN Features

author Lukas Vileikis tags MySQL SQL 9 min 2024-11-27
title

Massive SQL DELETE Operations and Database Purging Explained

author Lukas Vileikis tags DELETE SQL 4 min 2024-11-26
title

MySQL CAST: A Guide to the Data Conversion Function

author Antonello Zanini tags MySQL 3 min 2024-11-25

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗