intro
In this blog, we’re walking you through the Postgres foreign key constraint. Join us!
Have you ever stumbled upon a tangled web of tables in your PostgreSQL database, yearning for a way to fabricate order from the chaos? Enter the Postgres foreign key: your valiant thread, expertly linking tables and ensuring harmonious data relationships.
Imagine a bustling marketplace, overflowing with products and orders, yet each order is neatly tied to its rightful product. Foreign keys play that role, acting as silent keepers of referential integrity, preventing orphaned records, and maintaining the sanctity of our data kingdom. So, buckle up, database developers, as we journey to decode the puzzle of PostgreSQL foreign keys!
Introduction to the Postgres Foreign Key Constraint
In PostgreSQL, a foreign key is an accessory or qualifier uniquely identifying a row in another table. It refers to a column or set of columns and establishes a link between tables in a Postgres database.
It acts as a bridge between tables, enforcing defined relationships by ensuring that data in a child table corresponds to data in a parent table, thereby ensuring PostgreSQL data integrity.
How does the Postgres foreign key constraint do this? The foreign key constraint establishes a link between the data in two tables by referencing the primary key or a unique constraint of the referenced table.
The PostgreSQL Foreign Key Constraint Syntax
This is the syntax for defining a PostgreSQL foreign key constraint:
1
[CONSTRAINT fk_name]
2
FOREIGN KEY(fk_columns)
3
REFERENCES parent_table(parent_key_columns)
4
[ON DELETE delete_action]
5
[ON UPDATE update_action]
Breaking down each line of the syntax:
1.CONSTRAINT fk_name
2. FOREIGN KEY(fk_columns)
3. REFERENCES parent_table(parent_key_columns)
4. [ON DELETE delete_action]
5. [ON UPDATE update_action]
A simple example of how to create a table with a foreign key in Postgres is:
1
CREATE TABLE transactions (
2
transactions_id SERIAL PRIMARY KEY,
3
product_id INT,
4
quantity INT,
5
FOREIGN KEY (product_id) REFERENCES products(product_id)
6
);
In the above query, the transactions table has a foreign key constraint on the product_id column referencing the product_id column in the products table. This is to ensure that every product_id in the transactions table is in tune with a valid product_id in the products table, maintaining referential integrity.
Creating PostgreSQL Foreign Key Constraints
To create a foreign key constraint in PostgreSQL, we need to:
This is an example to understand the process better:
Suppose we have two tables, orders and transactions, and we want to create a foreign key relationship between them so that the orders table's id column references the order_id column in the transactions table. To do this, we would first create our parent table, orders:
1
CREATE TABLE orders (
2
id SERIAL PRIMARY KEY,
3
name VARCHAR(100)
4
);
Next, we will create the child table, transactions:
1
CREATE TABLE transactions (
2
id SERIAL PRIMARY KEY,
3
transaction_number VARCHAR(20),
4
order_id INT,
5
FOREIGN KEY (order_id) REFERENCES orders(id)
6
);
After running these queries in the SQL Commander of DbVisualizer, refresh the objects tree by right clicking on the tables node to view the list of all current tables in the database as shown below:
1
A view of the lists of all tables in the current database
In the transactions table, we defined the order_id column as a foreign key that references the id column in the orders table.
Notice that the Postgres foreign key constraint here will default to NO ACTION because the foreign key constraint does not have the ON DELETE and ON UPDATE action.
Other Postgres Foreign Key Constraint Actions
In this section of the blog, we’re going to expand on the example of modifying foreign key constraints by exploring different actions such as SET NULL, CASCADE, and SET DEFAULT.
SET NULL
One of the foreign key constraint actions is the SET NULL action. This action allows you to set the value of the foreign key column to NULL when the referenced row is updated or deleted.
Consider an example where we have two tables: employees and departments. The employees table has a foreign key constraint referencing the id column of the departments table. We want to set the foreign key column to NULL when the referenced department is deleted.
But first, we will create the sample tables with the foreign key that uses the SET NULL action in the ON DELETE clause:
1
CREATE TABLE departments (
2
id SERIAL PRIMARY KEY,
3
department_name VARCHAR(100)
4
);
1
CREATE TABLE employees (
2
id SERIAL PRIMARY KEY,
3
name VARCHAR(100),
4
department_id INT,
5
FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
6
);
1
The foreign key column set to NULL in DbVisualizer
Next, we will populate the deparments and employees tables:
1
INSERT INTO departments (department_name) VALUES
2
('IT'),
3
('Finance'),
4
('Production');
1
INSERT INTO employees (name, department_id) VALUES
2
('Jon Snow ', 1),
3
('Janet Villa', 2),
4
('Bobby Carson', 3);
1
Populating ‘departments’ and ‘employees’ tables
In this example, when a department is deleted from the departments table, the department_id column in the employees table will be set to NULL for all employees associated with the deleted department.
Now, let’s delete the department with id = 1 from the departments table:
1
DELETE FROM departments
2
WHERE id = 1;
Because of the ON DELETE SET NULL action, the referencing rows in the employees table are set to NULL.
Now that we’ve implemented a SET NULL action, we can verify the data in the departments table to determine the effect of the action by running the query below:
1
SELECT * FROM departments;
1
SET NULL action in DbVisualizer
From the query outputs above, we can see that the value of the department id 1 changed to NULL.
SET DEFAULT
The ON DELETE SET DEFAULT Postgres foreign key action allows you to set the value of the foreign key column to its default value when the referenced row is updated or deleted.
Consider an example where we have two tables: users and user_profiles. The user_profiles table has a foreign key constraint referencing the id column of the users table.
The goal is to set the foreign key column to its default value when the referenced user is deleted, but let’s first create the sample tables as shown below:
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
username VARCHAR(100)
4
);
And here's how we’ll create the user_profiles table with a foreign key constraint using SET DEFAULT action:
1
CREATE TABLE user_profiles (
2
id SERIAL PRIMARY KEY,
3
user_id INT,
4
profile_data TEXT,
5
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT
6
);
The next thing is to populate the users and users_profiles tables:
1
INSERT INTO users (username) VALUES
2
('Joy'),
3
('Leslie'),
4
('Carter');
1
INSERT INTO user_profiles (user_id, profile_data) VALUES
2
(1, 'Product Manager'),
3
(2, 'Software Engineer'),
4
(3, 'Technical Product Manager');
1
Populating ‘users’ and ‘user_profiles’ sample tables in DbVisualizer
In this example, we have specified ON DELETE SET DEFAULT as part of the foreign key constraint on the user_id column in the user_profiles table. This means that if a row is deleted from the users table and there are referencing rows in the user_profiles table, the user_id column in the user_profiles table will be set to its default value.
Now, say we delete a user from the users table:
1
DELETE FROM users
2
WHERE id = 1;
After executing the query, the row with id = 1 is deleted from the users table. Since there is a referencing row in the user_profiles table with user_id = 1, the user_id column in the user_profiles table will be set to its default value. The default value will depend on how you have defined it for the user_id column.
CASCADE
Another important Postgres foreign key constraint action is the ON DELETE CASCADE action. This particular PostgreSQL foreign key constraint allows you to automatically propagate changes to related rows when a referenced row is updated or deleted.
Consider an example where we have two tables: orders and order_items. The order_items table has a foreign key constraint referencing the id column of the orders table. We want to set the CASCADE action on deletion, so when an order is deleted, all associated order items are also removed.
Just as we did with the SET NULL action, we’ll first create the sample tables with the foreign key that uses the CASCADE action in the ON DELETE clause:
1
CREATE TABLE orders (
2
id SERIAL PRIMARY KEY,
3
order_number VARCHAR(20)
4
);
1
CREATE TABLE order_items (
2
id SERIAL PRIMARY KEY,
3
order_id INT,
4
item_name VARCHAR(100),
5
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
6
);
1
Creating ‘orders’ and ‘order_items’ sample tables in DbVisualizer
Now, populate the tables with data by running the query below:
1
INSERT INTO orders (order_number) VALUES
2
('001'),
3
('002'),
4
('003');
1
INSERT INTO order_items (order_id, item_name) VALUES
2
(1, 'Item A'),
3
(1, 'Item B'),
4
(2, 'Item C'),
5
(3, 'Item D');
1
Populating ‘orders’ and ‘order_items’ sample tables in DbVisualizer
In this example, when an order is deleted from the orders table, all corresponding order items in the order_items table will be automatically deleted. To do this, let’s run this query:
1
DELETE FROM orders
2
WHERE id = 001;
To verify the successful execution of the ON DELETE CASCADE action, run this query to display the data in the order_items table:
1
SELECT * FROM order_items;
Because of the ON DELETE CASCADE action, all the referencing rows in the order_items table are automatically deleted:
1
ON DELETE CASCADE action successfully executed in DbVisualizer
Great! We’re done experimenting with how some of these actions work. Time to look at some of the best practices for working with PostgreSQL foreign keys!
Best Practices for Working With Postgres Foreign Key
Below are some of the best practices that should be taken into consideration when working with a Postgres foreign key:
It’s a wrap!
Conclusion
Postgres foreign keys provide a powerful mechanism for establishing relationships between tables and enforcing referential integrity.
Foreign keys ensure that the values in the referencing column ie. child table correspond to the values in the referenced column, ie. parent table. This prevents orphaned rows and inconsistencies in the data. With respect to foreign key actions, PostgreSQL offers a variety of actions to handle updates and deletions of referenced rows, such as CASCADE, SET NULL, and SET DEFAULT, allowing you to define the desired behavior when changes occur.
To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including CASCADE, and SET NULL actions. Download DbVisualizer for free now!
Frequently Asked Questions
What are foreign keys, and why use them?
Foreign keys are like glue in your database, connecting related tables and preventing data inconsistencies. They ensure that, for example, an order always references a valid product, and a customer always has a valid address. Without them, you'd end up with "orphaned" records and messy data.
How do you define a foreign key in Postgres?
You use the FOREIGN KEY constraint in your CREATE TABLE or ALTER TABLE statement. Just tell Postgres which columns in your table link to specific columns in another table (like product_id referencing products.id).
Can I delete a row in a parent table if it has child rows?
It depends on what you set for ON DELETE when defining the foreign key. You can choose to automatically delete child rows (CASCADE), prevent deletion (RESTRICT), or even set the foreign key columns in child rows to null.
What are the available actions for handling updates and deletions with foreign keys in PostgreSQL?
