Postgres Foreign Key Constraint Tutorial

Introduction

If you’re working with data in PostgreSQL, it’s important to know how to use foreign keys. A foreign key is a specific type of SQL constraint that’s designed to maintain referential integrity between two tables. Put simply, a foreign key is a column or set of columns that establishes a link between data in two tables. The foreign key for one table references the primary key for the other table, thus creating a relationship between the tables. In this article, we’ll explain how to create a Postgres foreign key and look at some examples of its use.

Prerequisites

As we proceed with this tutorial, we’ll be working with tables that will have a foreign key relationship, so you’ll need to make sure that both Postgres and its psql command-line interface are installed. You’ll also need to have two tables that share that constraint for one of their respective columns.

Make sure that you have access to psql

You can use the psql -V command to verify that psql and Postgres are installed and working properly. Use the psql command by itself to connect to Postgres and enter the command-line interface.

NOTE: If you get an error with the message psql: FATAL: role "linux" does not exist, Postgres is probably attempting to use your system’s username to connect with psql. To remedy this, use the sudo -u postgres -i command to switch your username to postgres, then try to access psql again.

Screenshot of psql version and role "linux" does not exist error for Postgres

Create tables for the foreign key

The first thing we’ll do is create a database using the psql command-line interface:

We can create our PostgreSQL database using the basic command syntax shown below:

CREATE DATABASE db_name;

Here’s an example:

CREATE DATABASE testdb;

We can also create a table that serves as the “mother” table or a table that has a relationship with other tables. We’d use the basic syntax shown below:

CREATE TABLE TABLE_NAME(COLUMN_NAME + DATA_TYPE + CONSTRAINT [FOREIGN KEY])

For the purposes of this tutorial, we’ll create a table called price with a primary key id:

CREATE TABLE price(id INT PRIMARY KEY, price REAL);

In the next section, we’ll create another table that will have a foreign key relationship to the price table’s id column.

Using the foreign key constraint

As we mentioned earlier, a FOREIGN KEY constraint references the primary key in another table. We can have multiple foreign keys in a table; each key will be dependent on its relationship with some other table.

To create a table with a FOREIGN KEY constraint, you have to reference the other table. Let’s look at following example:

CREATE TABLE cars(
car_id INT NOT NULL,
price INT REFERENCES price (id),corresponding TO it
name TEXT,
brand TEXT);

Notice that the cars table has a foreign key that references the price table. This means that the values of the foreign key column(s) in the “child” table come from the “mother” table as a reference.

To see a table’s details, use the \d command followed by the table name:

TABLE "public.cars"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
--------+---------+-----------+----------+---------
car_id | INTEGER | | NOT NULL |
price | INTEGER | | |
name | text | | |
brand | text | | |
Foreign-KEY constraints:
"cars_price_fkey" FOREIGN KEY (price) REFERENCES price(id)

We could also declare the foreign key constraint by explicitly using the FOREIGN KEY and REFERENCES keywords when declaring the column name in our CREATE TABLE command:

CREATE TABLE cars(
car_id NOT NULL,
price INT,
name TEXT,
brand TEXT
PRIMARY KEY (car_id),
FOREIGN KEY (price) REFERENCES price(id);
);

At this point, you might be wondering what would happen if you accidentally deleted a record in the table cars— would you get an error? Actually, Postgres won’t allow you to delete the record unless the other corresponding table’s record is deleted as well.

You can use one of the following options– NO ACTION,DELETE CASCADE and DELETE RESTRICT— to decide what should happen when a record is deleted that’s linked to another table.

The following example shows the DELETE RESTRICT constraint being applied to a table:

CREATE TABLE cars(
car_id INT NOT NULL,
price INT REFERENCES price (id) ON DELETE RESTRICT,
name TEXT,
brand TEXT);

NOTE: This constraint will not allow you to delete a row unless all linked values are removed or the constraint itself is removed.

The next example shows the DELETE CASCADE constraint in action:

CREATE TABLE cars(
car_id INT NOT NULL,
price INT REFERENCES price (id) ON DELETE CASCADE,
name TEXT,
brand TEXT);

NOTE: The DELETE CASCADE constraint will allow you to delete a parent record, but it’s important to be aware that its corresponding child record will be deleted as well. Cascade deletion isn’t recommended for use in production since it’s too easy to unintentionally delete rows in another table.

The NO ACTION option is a default action in PostgreSQL that displays an error if one of the two constraints mentioned above are not specified.

Conclusion

In a relational database, it’s common for data in different tables to have certain links or relationships. A foreign key constraint provides an effective way to safeguard the referential integrity of this data, ensuring that data isn’t deleted from one table when a relationship exists with data in another table. This tutorial provided an overview of how a Postgres foreign key works and offered examples of how to use this constraint in different ways. With these examples to use as a guide, you’ll be able to implement foreign key relationships in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.