Foreign Key Configuration

Foreign keys are important constraints in PostgreSQL databases used to establish and maintain relationships between two tables. Foreign key constraints ensure referential integrity, preventing invalid data from being inserted or updated.

Configure Foreign Keys

You can configure foreign key constraints in the console or with SQL statements. The console is suitable for common table and column configuration, while SQL is better for bulk changes, migration scripts, and more advanced constraint configuration.

Console

1. Go to the CloudBase Console / PostgreSQL Database / Tables management page.
2. Select the target environment and table.
3. Click New Column, or edit an existing column.
4. Check Set as Foreign Key in the column configuration.
5. Select the referenced table and referenced column, then configure the delete rule, update rule, and other parameters.
6. Confirm the configuration and submit it. Wait until the foreign key constraint is created.

The referenced column usually needs to be the primary key or a unique column of the parent table. If the delete rule is Set Null, the foreign key column in the child table must allow NULL.

SQL

You can define a foreign key when creating a table, or add a foreign key constraint to an existing table. The following examples are standard PostgreSQL SQL statements.

Define a foreign key when creating tables:

CREATE TABLE public.departments (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE public.users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  department_id bigint,
  CONSTRAINT fk_user_department
    FOREIGN KEY (department_id)
    REFERENCES public.departments (id)
    ON DELETE SET NULL
    ON UPDATE NO ACTION
);

Add a foreign key to an existing table:

ALTER TABLE public.users
ADD CONSTRAINT fk_user_department
FOREIGN KEY (department_id)
REFERENCES public.departments (id)
ON DELETE SET NULL
ON UPDATE NO ACTION;

Before creating the foreign key, existing data must satisfy referential integrity: values in the child table's foreign key column must exist in the referenced column of the parent table, or be allowed NULL values. PostgreSQL does not automatically create an index on the child table's foreign key column. If the column is frequently used in joins, filters, or parent-table delete checks, create a separate index for it.

Foreign Key Parameters

Parameter	Description	Required	Example
Foreign Key Name	Name of the foreign key constraint	Yes	fk_user_department
Referenced Table	Name of the parent table referenced by the foreign key	Yes	departments
Referenced Column	Name of the referenced column in the parent table	Yes	id
Delete Rule	Action taken when a parent record is deleted	Yes	Cascade
Update Rule	Action taken when the referenced field in the parent table is updated	Yes	No Action

Delete and Update Rules

Rule	Identifier	Description
No Action	NO ACTION	Rejects the operation if referenced records exist in the child table
Restrict	RESTRICT	Similar to NO ACTION, immediately checks and rejects the operation
Cascade	CASCADE	Automatically propagates the operation to all referencing records in the child table
Set Null	SET NULL	Sets the foreign key field in the child table to NULL
Set Default	SET DEFAULT	Sets the foreign key field in the child table to its default value

tip

For more details and advanced usage of foreign key constraints, refer to the PostgreSQL Official Documentation - Foreign Keys.