Introduction and Preparation

This is an article to show about how to perform a specific task in the PostgreSQL Database. The task is to add a foreign key constraint in a certain table. In order to perform it, there are a few step create the necessary table to simulate it. The following are steps to execute in the form of several activities. Those steps are :

1. First step, connect to the PostgreSQL Database.

The execution to perform the task is done in a command line interface. In other words, it is the task to connect to the PostgreSQL Database in this article. Below is the command for connecting to PostgreSQL Database :

user@hostname:~# psql -Upostgres postgres
psql (10.5)
Type "help" for help.
postgres=# 

2. Create a new database. The following is the command or query for creating a new database named ‘test’ :

postgres=# create database test
CREATE DATABASE 
postgres=#

3. After successfully creating the new database, connect to the database. The following is the step for connecting to the database, just by typing ‘\c’ in PostgreSQL Command Console :

postgres=# \c test
You are now connected to database "test" as user "postgres".
postgres=#

4. Create two new tables.

In order to create the simulation, create two new tables. The two tables will show the relationship between them. One of the table will have a foreign key referencing to the column inside the other table. The first table name is ‘table_0’ and the second one is ‘table_1’. The following are the process for creating those tables :

test=# create table table_0(id serial primary key, name text);
CREATE TABLE
test=# create table table_1(id serial primary key, name text, parent_id int);
CREATE TABLE
test=#

5. Last but not least, try to describe both the two tables as follows :

                                                 ^
test=# alter table table_1 add constraint fk_table_0 foreign key(parent_id) references table_0(id) on update cascade on delete cascade;
ALTER TABLE
test=# \d+ table_0;
                                                Table "public.table_0"
 Column |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer |           | not null | nextval('table_0_id_seq'::regclass) | plain    |              | 
 name   | text    |           |          |                                     | extended |              | 
Indexes:
    "table_0_pkey" PRIMARY KEY, btree (id)
test=# \d+ table_1;
                                                  Table "public.table_1"
  Column   |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description 
-----------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id        | integer |           | not null | nextval('table_1_id_seq'::regclass) | plain    |              | 
 name      | text    |           |          |                                     | extended |              | 
 parent_id | integer |           |          |                                     | plain    |              | 
Indexes:
    "table_1_pkey" PRIMARY KEY, btree (id)
test=# 

Describing both two tables above has a certain purpose. It can be a proof for further comparison with the other output. It is Comparing with the description of the two tables after adding the foreign key constraint.

Adding Foreign Key to the Table in PostgreSQL Database

Using the above tables previously created, the following are the steps for adding foreign key to the table in PostgreSQL Database.

1. First of all, connect to the PostgreSQL Database.

The execution to perform the task is done in a command line interface. In other words, it is the task to connect to the PostgreSQL Database in this article. Below is the command for connecting to PostgreSQL Database :

user@hostname:~# psql -Uuser test
psql (10.5)
Type "help" for help.
test=# 

2. List the available tables in the connected PostgreSQL Database by typing the following command :

database=# \dt
                 List of relations
 Schema |           Name           | Type  | Owner 
--------+--------------------------+-------+-------
 public | table_0                  | table | postgres
 public | table_1                  | table | postgres
...
(xx rows)
database=# 

3. Add foreign key contraint.

test=# ALTER TABLE table_0 add constraint db_id_fkey foreign key(db_id) REFERENCES table_1(db_id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE
test=#

4. After adding the foreign key contraint.

                                                 ^
test=# alter table table_1 add constraint fk_table_0 foreign key(parent_id) references table_0(id) on update cascade on delete cascade;
ALTER TABLE
test=# \d+ table_0;
                                                Table "public.table_0"
 Column |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer |           | not null | nextval('table_0_id_seq'::regclass) | plain    |              | 
 name   | text    |           |          |                                     | extended |              | 
Indexes:
    "table_0_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "table_1" CONSTRAINT "fk_table_0" FOREIGN KEY (parent_id) REFERENCES table_0(id) ON UPDATE CASCADE ON DELETE CASCADE
test=# \d+ table_1;
                                                  Table "public.table_1"
  Column   |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description 
-----------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id        | integer |           | not null | nextval('table_1_id_seq'::regclass) | plain    |              | 
 name      | text    |           |          |                                     | extended |              | 
 parent_id | integer |           |          |                                     | plain    |              | 
Indexes:
    "table_1_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_table_0" FOREIGN KEY (parent_id) REFERENCES table_0(id) ON UPDATE CASCADE ON DELETE CASCADE
test=# 

The result of adding the foreign key constraint above is very clear. There is a ‘Referenced by’ information in the parent table. Another one is the ‘Foreign-key constraints’ information in the child table.