PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - Foreign Key

In PostgreSQL, a foreign key is a set of columns in a table that is used to ensure referential integrity in the database. A foreign key establishes a link between the data in two tables, creating a constraint that ensures that the values in one table correspond to the values in another table.

Key Concepts:

Referencing Table and Referenced Table: The table containing the foreign key is called the referencing table (or child table), and the table being referred to by the foreign key is called the referenced table (or parent table).
Referential Actions: These define the behavior of the foreign key in response to the operations (INSERT, UPDATE, DELETE) on the data:
- CASCADE: If a row in the referenced table is deleted or updated, the corresponding rows in the referencing table are automatically deleted or updated.
- SET NULL: If a row in the referenced table is deleted or updated, the foreign key values in the referencing table are set to NULL.
- SET DEFAULT: If a row in the referenced table is deleted or updated, the foreign key values in the referencing table are set to their default values.
- NO ACTION: If a row in the referenced table is deleted or updated, and there is a related foreign key value in the referencing table, it will result in a constraint violation.
- RESTRICT: It is similar to NO ACTION but checked immediately.

Creating a Foreign Key:

When you're creating a table, you can define a foreign key using the FOREIGN KEY constraint.

CREATE TABLE order_details (
    order_id int,
    product_id int,
    quantity int,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

In this example, the product_id column in the order_details table is a foreign key that refers to the product_id column in the products table.

You can also add a foreign key constraint to an existing table:

ALTER TABLE order_details
ADD FOREIGN KEY (product_id) REFERENCES products(product_id);

Dropping a Foreign Key:

To remove a foreign key constraint, you need to know the name of the constraint. You can drop it using the ALTER TABLE statement:

ALTER TABLE order_details
DROP CONSTRAINT constraint_name;

Enabling and Disabling a Foreign Key:

In PostgreSQL, you can temporarily disable and then re-enable a foreign key constraint:

-- Disable the constraint
ALTER TABLE order_details
ALTER CONSTRAINT constraint_name NOCHECK;

-- Enable and validate the constraint
ALTER TABLE order_details
VALIDATE CONSTRAINT constraint_name;

Advantages of Foreign Keys:

Data Integrity: Ensures that the relationship between tables is maintained consistently.
Data Accuracy: Helps in avoiding junk data or orphaned records in the database.
Query Optimization: Some query planners use foreign key information to create more efficient execution plans.

However, it's important to remember that while foreign keys are crucial for maintaining database integrity, they can introduce overhead during data insertion and update operations. Care should be taken to ensure that the benefits of data integrity outweigh the potential performance overhead in specific scenarios.

How to create a Foreign Key in PostgreSQL:

Description: Use the FOREIGN KEY constraint during table creation to establish a foreign key relationship.

Code:

CREATE TABLE table1 (
    id serial PRIMARY KEY,
    column1 INTEGER
);

CREATE TABLE table2 (
    id serial PRIMARY KEY,
    column2 INTEGER,
    table1_id INTEGER REFERENCES table1(id)
);

PostgreSQL Foreign Key example:

Description: An example of creating a foreign key relationship between two tables.

Code:

CREATE TABLE department (
    department_id serial PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employee (
    employee_id serial PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INTEGER REFERENCES department(department_id)
);

Foreign Key constraints in PostgreSQL:

Description: The FOREIGN KEY constraint enforces referential integrity between tables.

Code:

CREATE TABLE parent (
    id serial PRIMARY KEY
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent_id INTEGER REFERENCES parent(id)
);

CASCADE option in PostgreSQL Foreign Key:

Description: When a referenced row is deleted, update the foreign key values in the referencing table.

Code:

CREATE TABLE parent (
    id serial PRIMARY KEY
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE
);

NO ACTION in PostgreSQL Foreign Key:

Description: Rejects the delete or update operation on the referenced row if there is a dependent row.

Code:

CREATE TABLE parent (
    id serial PRIMARY KEY
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent_id INTEGER REFERENCES parent(id) ON DELETE NO ACTION
);

ON DELETE and ON UPDATE with Foreign Key in PostgreSQL:

Description: Specify actions on delete and update operations on the referenced rows.

Code:

CREATE TABLE parent (
    id serial PRIMARY KEY
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE ON UPDATE SET NULL
);

Checking Foreign Key constraints in PostgreSQL:
- Description: Use the pg_constraint system catalog to check foreign key constraints.
- Code:
```
SELECT conname, confrelid::regclass AS table_name
FROM pg_constraint
WHERE confrelid = 'child'::regclass;
```
Dropping Foreign Key in PostgreSQL:
- Description: Remove a foreign key constraint from a table.
- Code:
```
ALTER TABLE child
DROP CONSTRAINT child_parent_id_fkey;
```
Adding Foreign Key to an existing table in PostgreSQL:
- Description: Add a foreign key constraint to an existing table.
- Code:
```
ALTER TABLE child
ADD CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES parent(id);
```
Foreign Key vs. Unique Constraint in PostgreSQL:
- Description: While both ensure data integrity, a foreign key establishes a relationship between tables, while a unique constraint ensures unique values within a single table.
- Code (Unique Constraint):
```
CREATE TABLE example (
    id serial PRIMARY KEY,
    unique_column INTEGER UNIQUE
);
```

Composite Foreign Key in PostgreSQL:

Description: Use multiple columns to create a composite foreign key.

Code:

CREATE TABLE parent (
    id serial PRIMARY KEY,
    column1 INTEGER,
    column2 INTEGER
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent_id INTEGER,
    FOREIGN KEY (parent_id, column1) REFERENCES parent(id, column2)
);

Deferring Foreign Key constraints in PostgreSQL:
- Description: Defer checking foreign key constraints until the end of the transaction.
- Code:
```
SET CONSTRAINTS ALL DEFERRED;

-- Your transactions here

COMMIT;
```

Foreign Key references to multiple tables in PostgreSQL:

Description: A single table can have foreign key references to multiple tables.

Code:

CREATE TABLE parent1 (
    id serial PRIMARY KEY
);

CREATE TABLE parent2 (
    id serial PRIMARY KEY
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent1_id INTEGER REFERENCES parent1(id),
    parent2_id INTEGER REFERENCES parent2(id)
);

Foreign Key naming conventions in PostgreSQL:

Description: Name foreign keys using a meaningful and consistent convention.

Code:

CREATE TABLE parent (
    id serial PRIMARY KEY
);

CREATE TABLE child (
    id serial PRIMARY KEY,
    parent_id INTEGER REFERENCES parent(id) CONSTRAINT fk_child_parent
);

Using ALTER TABLE to modify Foreign Key in PostgreSQL:

Description: Use ALTER TABLE to modify foreign key constraints, including adding or removing them.

Code:

ALTER TABLE child
ADD CONSTRAINT new_fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id);

ALTER TABLE child
DROP CONSTRAINT old_fk_child_parent;

Indexing Foreign Keys in PostgreSQL:
- Description: Index foreign keys for improved query performance.
- Code:
```
CREATE INDEX idx_fk_child_parent
ON child(parent_id);
```

Circular Foreign Key references in PostgreSQL:

Description: Avoid circular references, as PostgreSQL does not support them directly.

Code (Not Supported):

CREATE TABLE table1 (
    id serial PRIMARY KEY,
    table2_id INTEGER REFERENCES table2(id)
);

CREATE TABLE table2 (
    id serial PRIMARY KEY,
    table1_id INTEGER REFERENCES table1(id)
);