PostgreSQL

Foreign keys are a fundamental aspect of relational database design. They ensure that relationships between tables are maintained consistently by enforcing referential integrity. A foreign key in one table points to a primary key in another table, which helps establish a link between the two tables and ensures that the data remains accurate and consistent.

Here's a basic guide on how to work with foreign keys in PostgreSQL, complete with code samples that relate to a banking system involving customers, accounts, and transactions.

1. Creating Tables with Foreign Keys

   When creating tables, you can define a foreign key that references the primary key of another table. This ensures that the values in the foreign key column must match values in the referenced table.

   -- Create the 'customers' table
   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       email VARCHAR(100) UNIQUE NOT NULL
   );
   
   -- Create the 'accounts' table with a foreign key referencing 'customers'
   CREATE TABLE accounts (
       account_id SERIAL PRIMARY KEY,
       customer_id INT NOT NULL,
       balance DECIMAL(15, 2) NOT NULL,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );
   
   -- Create the 'transactions' table with a foreign key referencing 'accounts'
   CREATE TABLE transactions (
       transaction_id SERIAL PRIMARY KEY,
       account_id INT NOT NULL,
       transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       amount DECIMAL(15, 2) NOT NULL,
       FOREIGN KEY (account_id) REFERENCES accounts(account_id)
   );
   

2. Adding Foreign Keys to Existing Tables

   If you need to add a foreign key to an existing table, use the ALTER TABLE statement.

   -- Add a foreign key to the 'accounts' table
   ALTER TABLE accounts
   ADD CONSTRAINT fk_customer
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
   
   -- Add a foreign key to the 'transactions' table
   ALTER TABLE transactions
   ADD CONSTRAINT fk_account
   FOREIGN KEY (account_id) REFERENCES accounts(account_id);
   

3. Dropping Foreign Keys

   To remove a foreign key constraint, you must first identify the constraint name and then use the ALTER TABLE statement to drop it.

   -- Drop the foreign key constraint from the 'accounts' table
   ALTER TABLE accounts
   DROP CONSTRAINT fk_customer;
   
   -- Drop the foreign key constraint from the 'transactions' table
   ALTER TABLE transactions
   DROP CONSTRAINT fk_account;
   

4. Foreign Key Constraints Options

   Foreign keys in PostgreSQL can also be configured with various options:

   • ON DELETE CASCADE: Automatically delete rows in the child table when the corresponding row in the parent table is deleted.
   • ON UPDATE CASCADE: Automatically update the foreign key value in the child table when the corresponding primary key value in the parent table is updated.
   • ON DELETE SET NULL: Set the foreign key column to NULL when the corresponding row in the parent table is deleted.
   • ON UPDATE SET NULL: Set the foreign key column to NULL when the corresponding primary key value in the parent table is updated.

   -- Create 'accounts' table with ON DELETE CASCADE
   CREATE TABLE accounts (
       account_id SERIAL PRIMARY KEY,
       customer_id INT NOT NULL,
       balance DECIMAL(15, 2) NOT NULL,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
   );
   
   -- Create 'transactions' table with ON UPDATE SET NULL
   CREATE TABLE transactions (
       transaction_id SERIAL PRIMARY KEY,
       account_id INT NOT NULL,
       transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
       amount DECIMAL(15, 2) NOT NULL,
       FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON UPDATE SET NULL
   );
   

   By using foreign keys, you can maintain the integrity and consistency of your data across related tables in PostgreSQL.






5. Understanding Foreign Keys in Database Design

   A foreign key in database design is a column (or set of columns) in one table that uniquely identifies a row of another table. It's a key used to link two tables together. This concept might seem a bit abstract, so let's use an analogy to simplify it, followed by a basic code example to illustrate how it works in practice.

   Analogy: Cities and Countries

   Imagine a world map with countries and their cities. Each country can have multiple cities. To represent this relationship in a database, you would have two tables: a Countries table and a Cities table.

   • The Countries table has a primary key called CountryID that uniquely identifies each country.
   • The Cities table lists cities around the world, and each city is associated with a country.

   In this analogy, the CountryID field in the Cities table acts as a foreign key. It references the CountryID primary key in the Countries table. This setup ensures that each city in the Cities table is linked to a specific country in the Countries table. Just as you would use a country's name to find its cities on a map, in a database, you use the foreign key to retrieve all cities belonging to a particular country.

   Code Example

   Let's translate this analogy into a simple SQL code example to demonstrate how foreign keys work in practice.

   SQL Table Creation

   -- Create Countries table
   CREATE TABLE Countries (
       CountryID INT NOT NULL,
       CountryName VARCHAR(255) NOT NULL,
       PRIMARY KEY (CountryID)
   );
   
   -- Create Cities table
   CREATE TABLE Cities (
       CityID INT NOT NULL,
       CityName VARCHAR(255) NOT NULL,
       CountryID INT,
       PRIMARY KEY (CityID),
       FOREIGN KEY (CountryID) REFERENCES
   Countries(CountryID)
   );
   

   In this example:

   • The Countries table is created with CountryID as its primary key.
   • The Cities table is created with CityID as its primary key and CountryID as a foreign key.
   • The FOREIGN KEY (CountryID) REFERENCES Countries(CountryID) line in the Cities table definition establishes CountryID as a foreign key that references the CountryID primary key in the Countries table.

   This setup allows the database to understand the relationship between countries and their cities. For instance, if you have a country with CountryID = 1, and you want to add cities to this country in the Cities table, you would set CountryID = 1 for these cities. The database then knows these cities belong to the country with CountryID = 1.

   Conclusion

   Foreign keys serve as crucial links between tables in relational databases, enabling the representation of real-world relationships like the one between countries and cities. They ensure data integrity by only allowing the insertion of records that have a corresponding value in the linked table. Through foreign keys, databases can maintain accurate and consistent relationships between data points across different tables.

   PRODUCT TYPE ID AS FOREIGN KEY IN PRODUCT TABLE

   

   ORDER ID AS FOREIGN KEY IN ORDER DETAIL TABLE

   

   DEPARTMENT ID AS FOREIGN KEY IN EMPLOYEE TABLE