PostgreSQL - Create table with FOREIGN KEY

In this article, we would like to show you how to create a table with FOREIGN KEY in PostgreSQL.

Quick solution:

CREATE TABLE "table1_name" (
    "column1" DATA_TYPE,
    "column2" DATA_TYPE,
    "columnN" DATA_TYPE,
    PRIMARY KEY ("column_name"),
    FOREIGN KEY ("column_name") REFERENCES "table2_name"("reference_column")
);

Note:

Go to the official documentation to see available DATA_TYPES.

Practical example

In this example, we will create two tables:

• groups table with two columns:
  • id- with PRIMARY KEY constraint
  • group_name

and users table with three columns:

• id - with PRIMARY KEY constraint
• username
• group_id - which will be our FOREIGN KEY with groups.id column reference

Query:

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"),
	FOREIGN KEY ("group_id") REFERENCES "groups"("id")
);

Result:

As we see in the second picture, the group_id column from users table has FOREIGN KEY constraint which refers to the id column from groups table with PRIMARY KEY constraint.

References

• PostgreSQL Data Types