Foreign keys help normalize data in databases and reduce redundancy. In fact, it is the FOREIGN KEY mechanism that provides the relationality of relational databases. Read on to find out what foreign keys in PostgreSQL are and how to use them.
Contents
- Learn the basics of the PostgreSQL FOREIGN KEY constraint
- Add FOREIGN KEY while creating a table
- Add FOREIGN KEY to an existing table
- How to drop FOREIGN KEY in PostgreSQL
- Why is a GUI tool a better way to manage keys in PostgreSQL?
- Summary
This is the second article in our series on PostgreSQL database constraints, and it comprises a follow-up to our recent PostgreSQL Primary Keys review. Both primary keys and foreign keys are the two types of constraints that are used to enforce data integrity in PostgreSQL tables. Here we will go into the basics of PostgreSQL Foreign Keys, demonstrate to you how to create, drop, and manage foreign keys on real-life examples, and introduce to you one of the most convenient Postgres GUI tools on the market.
Learn the basics of the PostgreSQL FOREIGN KEY constraint
What is FOREIGN KEY in PostgreSQL?
A foreign key (FK) represents one or more than one column used to establish and enforce a link between data in two database tables for controlling data stored in the foreign key table.
The database table that contains a foreign key is called the child table (or the referencing table). And the table referenced by the foreign key is called the parent table (or the referenced table).
The foreign key is often called the referencing key as it matches the primary key field from another table. In other words, the foreign key field in one table refers to another table’s primary key field.
Technically, FOREIGN KEYs, alongside PRIMARY KEYs, CHECK and UNIQUE constraints, belong to the PostgreSQL database constraints. Constraints are the rules enforced on data columns in tables and used to prevent invalid data from being entered into the database.
Foreign key example
For better understanding, let us consider the following example. Suppose, we have the sportspeople table that has a PostgreSQL foreign key column sports_id. This foreign key refers to the primary key column sports_id in the sports table. This way, it forms one-to-many relationships between the sportspeople and sports tables, which means that for one sport there could be multiple sportspeople. In other words, multiple records in the sportspeople table can contain the same sports_id that points to one sports_id value in the sports table.
CREATE TABLE sports(
sport_id INT,
sport_name VARCHAR(255) NOT NULL,
PRIMARY KEY(sport_id)
);
CREATE TABLE sportspeople(
sportsperson_id INT,
sport_id INT,
sportperson_name VARCHAR(255) NOT NULL,
PRIMARY KEY(sportsperson_id),
CONSTRAINT fk_sport_id
FOREIGN KEY(sport_id)
REFERENCES sports(sport_id)
);
Note
A PostgreSQL table can have various foreign keys depending on its connection with other tables.
Add FOREIGN KEY while creating a table
How to create a foreign key in PostgreSQL? Actually, you can do it in three ways: define the foreign key inside the CREATE TABLE statement, run a separate ALTER TABLE statement in case you want to add a foreign key to an already existing table, or create a FOREIGN KEY constraint visually in a GUI tool for PostgreSQL.
Let’s look at how to create a PostgreSQL foreign key when creating a table. The syntax for this looks as follows.
CONSTRAINT [constraint_name] FOREIGN KEY(fk_columns) REFERENCES (parent_table_columns) [ON DELETE ] [ON UPDATE ]
In the Postgres foreign key syntax above, specify the following:
constraint_name is the name to be used to define the foreign key constraint. Please note that if you do not specify the foreign key name, PostgreSQL will create the constraint name automatically as per the default naming convention.
fk_columns are the table columns to be used as a foreign key.
parent_table parameter defines the name of the parent table, followed by its column names, which are referenced by the foreign key.
ON DELETE and ON UPDATE clauses are optional. These actions determine the behavior of the PostgreSQL server when a primary key is deleted or updated in the parent table. The ON DELETE clause specifies the action to be performed when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to be taken when a referenced column in the referenced table is being updated to a new value.
PostgreSQL supports the following referential actions:
- NO ACTION
- RESTRICT
- SET NULL
- SET DEFAULT
- CASCADE
Let’s look at each of them in more detail.
NO ACTION
The NO ACTION referential action produces an error indicating that the data deletion or update would create a foreign key constraint violation. This is the default action.
The following example demonstrates how NO ACTION works. Suppose, we have two tables.
CREATE TABLE sports(
sport_id INT,
sport_name VARCHAR(255) NOT NULL,
PRIMARY KEY(sport_id)
);
CREATE TABLE sportspeople(
sportsperson_id INT,
sport_id INT,
sportperson_name VARCHAR(255) NOT NULL,
PRIMARY KEY(sportsperson_id),
CONSTRAINT fk_sport_id
FOREIGN KEY(sport_id)
REFERENCES sports(sport_id)
ON DELETE NO ACTION
);
Let us try to delete one record from the sports table.
DELETE FROM sports WHERE sport_id = 2;
As you can see, the server issues an error indicating that the action we are going to take violates the existing foreign key constraint.
RESTRICT
The RESTRICT action is pretty much the same as NO ACTION except that the check is not deferrable. RESTRICT prevents the deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is thrown.
SET NULL
If a foreign key is created with ON DELETE SET NULL or ON UPDATE SET NULL, then when deleting or updating data in the parent table, referencing columns in the child table will be automatically set to NULL.
Let us look at how this works. Suppose, we have created a foreign key on the sportspeople table with ON DELETE SET NULL. And now we are deleting rows from its parent table – sports.
As you can see from the output, the row that had sport_id equal to 1 now has it set to NULL.
SET DEFAULT
If a foreign key is created with ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT, then when deleting or updating data in the parent table, the foreign key column in the child table will be automatically set to the default value in case one is specified.
Let’s look at how the SET DEFAULT action works.
First, we create the tables.
Then, we try to delete one row in the sports table, which is the parent table in our tandem.
DELETE FROM sports WHERE sport_id = 1;
Now, let’s see what happened to the data in the child table.
We defined the foreign key constraint with ON DELETE SET DEFAULT, so the row referencing the deleted row is now set to the DEFAULT value, which equals 2.
CASCADE
The ON DELETE CASCADE is the most commonly used option as it automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted.
Respectively, ON UPDATE CASCADE allows changing the primary key value, and all the tables that have foreign key references to that value will be changed accordingly. Let’s see how it works.
Once again we create our two tables – sports and sportspeople – but this time we add a foreign key with ON UPDATE CASCADE.
Now we update the sports table, changing the value in the primary key column.
UPDATE sports SET sport_id = 5 WHERE sport_id = 1;
Let’s look at how this affected the data in the child sportspeople table.
From the output, you can clearly see that the sport_id value in the child table has been changed according to the change made in the parent table. Incredible, right?
Add FOREIGN KEY to an existing table
In PostgreSQL, you can add a foreign key to an existing table by using the ALTER TABLE statement.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers (id);
Note:
In PostgreSQL, to add or drop a foreign key, the ALTER TABLE statement can be used.
However, what if you need to add a new column to be used as a foreign key? No problem, first create a column.
ALTER TABLE orders
ADD COLUMN IF NOT EXISTS city_id (id);
And then add a foreign key to it.
ALTER TABLE orders
ADD CONSTRAINT fk_city_id FOREIGN KEY (city_id) REFERENCES city (id);
How to drop FOREIGN KEY in PostgreSQL
You can remove a foreign key constraint in PostgreSQL using the ALTER TABLE statement.
ALTER TABLE table_name DROP CONSTRAINT foreign_key_name;
To drop a Postgres foreign key, in the syntax above, just specify the table name and the foreign key name, and you’re all set to go.
ALTER TABLE sportspeople DROP CONSTRAINT fk_sport_id;
Why is a GUI tool a better way to manage keys in PostgreSQL?
PostgreSQL is an open-source relational database management system that provides enterprise-level performance and delivers a superb feature set. It is available for all popular operating systems and adds value to millions of businesses worldwide. All this makes PostgreSQL one of the most popular DBMSs on the market today.
Interacting with the PostgreSQL server by issuing commands via the command line is not that easy for newbies and inexperienced users: you need to memorize those commands, and the error probability is high. That’s why lots of PostgreSQL developers, admins, and analysts choose GUI tools as they significantly facilitate the work with PostgreSQL and deliver exceptional user experiences.
We want to introduce to you one of the best IDEs for developing, managing, and maintaining PostgreSQL databases you can find – dbForge Studio for PostgreSQL. The tool comprises an advanced GUI designed to cover all possible database-related tasks.
Managing keys has never been easier. When writing queries you can enjoy instant syntax check and context-sensitive code prompts. More than that, you do not need to memorize the names of tables and columns – just select the required ones from the suggestions list. dbForge Studio’s robust Master-Detail Browser allows viewing and analyzing related data. Cool, right? You definitely need to try it!
Summary
Foreign keys are crucial to the PostgreSQL database design — they help define the relationship between tables. When creating a constraint on a table, all data deletions and updates must conform to the corresponding rule. This ensures that the relationship between rows in two tables will remain synchronized after all possible database changes.
Try before you buy
Download a free 30-day trial of dbForge Studio for PostgreSQL, and see how it can handle your database challenges.
