Postgres Foreign Key Constraint Tutorial
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.
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.
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:
Here’s an example:
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:
For the purposes of this tutorial, we’ll create a table called
price with a primary key
In the next section, we’ll create another table that will have a foreign key relationship to the price table’s
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:
car_id INT NOT NULL,
price INT REFERENCES price (id),corresponding TO it
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:
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
car_id | INTEGER | | NOT NULL |
price | INTEGER | | |
name | text | | |
brand | text | | |
"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:
car_id NOT NULL,
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–
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:
car_id INT NOT NULL,
price INT REFERENCES price (id) ON DELETE RESTRICT,
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:
car_id INT NOT NULL,
price INT REFERENCES price (id) ON DELETE CASCADE,
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.
NO ACTION option is a default action in PostgreSQL that displays an error if one of the two constraints mentioned above are not specified.
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