Postgres ALTER TABLE example - adding a foreign key constraint

Adding a foreign key (otherwise known as a relation or association) to a Postgres table means adding a 'constraint'. These basic examples assume that the column type is correct, and any existing values match existing relation IDs in the target table.

Creating a simple foreign key relation, Note we don't even need to specify the name of the constraint, the database will give us one automatically.

content_copyCopy

ALTER TABLE
  "users"
ALTER TABLE
  "public"."users"
ADD
  CONSTRAINT FOREIGN KEY ("department_id") REFERENCES "departments" ("id")

In this example we're explicitly telling the database what action to take when a record is updated or deleted. CASCADE on delete is useful - it will will delete this record if the foreign key relation is deleted.

content_copyCopy

ALTER TABLE
  "users"
ALTER TABLE
  "public"."users"
ADD
  CONSTRAINT "example_fk_1" FOREIGN KEY ("department_id") REFERENCES "departments" ("id") ON
UPDATE
  NO ACTION ON DELETE CASCADE

Using Beekeeper Studio?

Beekeeper Studio is a free and open source database manager for Windows, Mac, and Linux.

This feature is baked right in, so there's no need to manually type SQL every time. Access foreign key editing by right-clicking on a table in the sidebar and clicking 'View Structure', then click 'Relations'.

Download Beekeeper Studio