Hello! I will cover How to Create Foreign Key PostgreSQL topic in this article.

How to Create Foreign Key PostgreSQL

What is a Foreign Key

Also known as foreign constraint. Foreign keys are used to maintain referential integrity of data.

Explaining this with an example would be simpler. For instance we are designing an e-commerce website, there should be a table for users, and one table for orders. Each order, should have a relation with users table over a foreign key. If you try to insert a record to orders table, foreign key is going to check users table first to reject or approve the insertion.

Foreign key is a very simple method to keep referential integrity of data. Without foreign keys you should do select before inserting referential data.

You can create foreign key while creating table, or you can add a foreign constraint after creating table. But as a note, if referential integrity check fails while adding foreign key, statement will fail.

Creating Foreign Key with Dbeaver

Creating Foreign Key with Tables

Right click to tables in Database navigator and create a new users table as following:

Create a primary key to column id and unique key with mail.

Click save under right bottom of screen.

Click persist.

Create orders table as following.

On left, click to Foreign Keys tab and create a foreign key referencing to id column on users table.

Select referencing table, key, column and click ok.

Click save and click persist to save table.

If you have troubles with connecting a database using dbeaver you can refer to my article about connecting PostgreSQL using Dbeaver..

Creating Foreign Key After Tables

Create users table as same as above and orders table without foreign key. To add a foreign key open orders table and switch to foreign keys tab. Right click and click Create new Foreign Key button.

Select keys as needed and click ok. Click save on bottom right of the screen and click persist to save to database.

Creating Foreign Key with psql

Creating Foreign Key with Tables

As you can see below, you can mention foreign keys while creating tables.

postgres=# CREATE TABLE public.users (
        id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        mail varchar(100) NULL,
        "name" varchar(150) NULL,
        CONSTRAINT users_pk PRIMARY KEY (id),
        CONSTRAINT users_un UNIQUE (mail)
);
CREATE TABLE
postgres=# CREATE TABLE public.orders (
        order_id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
        user_id int4 NULL,
        total_cost numeric NULL,
        CONSTRAINT orders_pk PRIMARY KEY (order_id),
        CONSTRAINT orders_fk FOREIGN KEY (user_id) REFERENCES public.users(id)
);

Creating Foreign Key After Tables

You can create table without foreign keys, and alter table with a foreign key.

postgres=# CREATE TABLE public.users (
        id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        mail varchar(100) NULL,
        "name" varchar(150) NULL,
        CONSTRAINT users_pk PRIMARY KEY (id),
        CONSTRAINT users_un UNIQUE (mail)
);
postgres=# CREATE TABLE public.orders (
        order_id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
        user_id int4 NULL,
        total_cost numeric NULL,
        CONSTRAINT orders_pk PRIMARY KEY (order_id)
);


postgres=# ALTER TABLE public.orders ADD CONSTRAINT orders_fk FOREIGN KEY (user_id) REFERENCES public.users(id);
ALTER TABLE

To Sum Up

Until here, I mentioned 4 options about how to create foreign key PosgtreSQL.

On Delete actions topic has not been covered in this article, on delete actions will be covered in upcoming articles.

If you have a question or comment, please leave a comment below.