EN
PostgreSQL - add FOREIGN KEY to existing table
0 points
In this article, we would like to show you how to add FOREIGN KEY to the existing table in PostgreSQL.
Quick solution:
xxxxxxxxxx1
ALTER TABLE2
"table_name"3
ADD CONSTRAINT 4
"constraint_name" FOREIGN KEY ("column_name")5
REFERENCES "reference_table"("reference_column");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.
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:
xxxxxxxxxx1
ALTER TABLE "users"2
ADD CONSTRAINT "fk_group_id" FOREIGN KEY ("group_id") REFERENCES "groups"("id");Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE "groups" (2
"id" SERIAL,3
"group_name" VARCHAR(50) NOT NULL,4
PRIMARY KEY ("id")5
);6
7
CREATE TABLE "users" (8
"id" SERIAL,9
"username" VARCHAR(50) NOT NULL,10
"group_id" INTEGER,11
PRIMARY KEY ("id")12
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO "groups"2
("group_name")3
VALUES4
('group1'),5
('group2');6
7
INSERT INTO "users"8
("username", "group_id")9
VALUES10
('user1', 1),11
('user2', 1),12
('user3', 2),13
('user4', 2);