PostgreSQL - add FOREIGN KEY to existing table

In this article, we would like to show you how to add FOREIGN KEY to the existing table in PostgreSQL.

Quick solution:

ALTER TABLE
    "table_name"
ADD CONSTRAINT 
    "constraint_name" FOREIGN KEY ("column_name")
    REFERENCES "reference_table"("reference_column");

Practical example

To show how to add FOREIGN KEY to the existing table, we will use the following tables

Note:

At the end of this article you can find database preparation SQL queries.

Example

In this example, we will add fk_group_id foreign key to the group_id column from users table which is a reference to the id column from groups table.

Query:

ALTER TABLE "users"
ADD CONSTRAINT "fk_group_id" FOREIGN KEY ("group_id") REFERENCES "groups"("id");

Output:

Database preparation

create_tables.sql file:

CREATE TABLE "groups" (
	"id" SERIAL,
	"group_name" VARCHAR(50) NOT NULL,
	PRIMARY KEY ("id")
);

CREATE TABLE "users" (
	"id" SERIAL,
	"username" VARCHAR(50) NOT NULL,
	"group_id" INTEGER,
	PRIMARY KEY ("id")
);

insert_data.sql file:

INSERT INTO "groups"
    ("group_name")
VALUES
    ('group1'),
	('group2');

INSERT INTO "users"
    ("username", "group_id")
VALUES
    ('user1', 1),
    ('user2', 1),
    ('user3', 2),
	('user4', 2);