Foreign Key Constraint - (PostgreSQL Constraints)

In PostgreSQL, a foreign key constraint is a way to enforce referential integrity between related tables. It ensures that the values in a column of one table match the values in another column of a different table. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the foreign key constraint in PostgreSQL constraints.

Syntax

CREATE TABLE table_name (
   column1 datatype [constraint],
   column2 datatype [constraint],
   ...
   CONSTRAINT fk_constraint FOREIGN KEY (column)
      REFERENCES parent_table (column)
      [ON DELETE action]
      [ON UPDATE action]
);

• table_name: The name of the table you're creating.
• column1, column2, ...: The columns that make up the table.
• fk_constraint: The name of the foreign key constraint.
• column: The column in the child table that references the parent table.
• parent_table: The name of the parent table.
• action: The action to take when a row is deleted or updated in the parent table.

Example

Let's create two tables, orders and customers, and set up a foreign key constraint between them.

CREATE TABLE customers (
   id SERIAL PRIMARY KEY,
   name VARCHAR(255),
   email VARCHAR(255)
);

CREATE TABLE orders (
   id SERIAL PRIMARY KEY,
   customer_id INTEGER,
   total DECIMAL(10,2),
   CONSTRAINT fk_orders_customer_id
      FOREIGN KEY (customer_id)
      REFERENCES customers (id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

This will create two tables, customers and orders, and set up a foreign key constraint between the customer_id column in the orders table and the id column in the customers table.

Explanation

In this example, we created two tables, customers and orders. We then created a foreign key constraint between the orders table and the customers table using the CONSTRAINT statement and specifying the FOREIGN KEY and REFERENCES clauses.

The FOREIGN KEY clause defines the column in the child table (orders) that references the parent table (customers). The REFERENCES clause specifies the parent table and the column that is being referenced.

The ON DELETE and ON UPDATE clauses specify the action to take when a row is deleted or updated in the parent table. In our example, we used CASCADE to ensure that when a row is deleted or updated in the customers table, all related rows in the orders table are also deleted or updated accordingly.

Use

The foreign key constraint in PostgreSQL is useful for ensuring data integrity between related tables. It ensures that any changes made to a primary key in the parent table are propagated to all related rows in the child table.

Important Points

• The foreign key column in the child table must have the same data type as the referenced column in the parent table.
• The parent column referenced by the foreign key must have an index.
• The behavior of the foreign key constraint can be set on a case-by-case basis by using the ON DELETE and ON UPDATE clauses.
• When using the CASCADE action, be careful not to accidentally delete all rows in a child table by deleting a single row in a parent table.

Summary

In this tutorial, we discussed the foreign key constraint in PostgreSQL constraints. We covered the syntax, example, output, explanation, use, important points, and summary of the foreign key constraint. With this knowledge, you can now create foreign key constraints to ensure data integrity between related tables in your PostgreSQL database.