Foreign Key

 

1. What is a Foreign Key?

A FOREIGN KEY in SQL is a constraint that creates a relationship between two tables. It links a column or group of columns in one table (child table) to a primary key or unique key in another table (parent table). This ensures referential integrity, meaning that any value in the foreign key column must exist in the primary key column of the parent table.

2. Creating Foreign Keys

Foreign keys can be defined:

• During table creation with the CREATE TABLE statement.
• After table creation with the ALTER TABLE statement.

2.1. Defining a Foreign Key in CREATE TABLE

Here’s the syntax for adding a foreign key while creating a table:

CREATE TABLE child_table (

    child_column datatype,

    ...

    FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)

);

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    name VARCHAR(100)

);

Example

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    order_date DATE,

    customer_id INT,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

In this example, customer_id in the orders table is a foreign key that references customer_id in the customers table.

2.2. Adding a Foreign Key with ALTER TABLE

To add a foreign key after creating a table, use the ALTER TABLE statement:

sql
ALTER TABLE child_table
ADD FOREIGN KEY (child_column) REFERENCES parent_table(parent_column);

Example

sql
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

3. Naming Foreign Key Constraints

You can give a foreign key constraint a specific name using the CONSTRAINT keyword. This makes it easier to reference and manage the constraint later.

sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

In this example, the foreign key is named fk_customer.

4. Cascade Options for Foreign Keys

Foreign key constraints can define actions to take when a referenced row in the parent table is updated or deleted. Common cascade options include:

• ON DELETE CASCADE: Deletes all rows in the child table that reference the deleted row in the parent table.
• ALTER TABLE orders ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE CASCADE;
• 
  
• ON UPDATE CASCADE: Updates the foreign key in the child table if the referenced key in the parent table is updated.

ALTER TABLE orders 

ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) 

ON UPDATE CASCADE;

• ON DELETE SET NULL: Sets the foreign key to NULL in the child table when the referenced row in the parent table is deleted.

ALTER TABLE orders ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE SET NULL;

• ON DELETE RESTRICT: Prevents the deletion of a row in the parent table if it is referenced in the child table.
• ALTER TABLE orders ADD FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE RESTRICT;

Example with Cascade Options

sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

In this example:

• If a customer_id in customers is deleted, all related orders will also be deleted (ON DELETE CASCADE).
• If customer_id in customers is updated, the customer_id in orders is also updated (ON UPDATE CASCADE).

5. Managing Foreign Keys

Managing foreign keys includes dropping, disabling, and checking for any constraint violations.

5.1 Dropping a Foreign Key

To remove a foreign key constraint, use the DROP command with ALTER TABLE:

sql
ALTER TABLE child_table
DROP FOREIGN KEY constraint_name;

Example

sql
ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

5.2 Disabling and Enabling Foreign Key Checks

In some databases (like MySQL), you can temporarily disable foreign key checks, which may be helpful for bulk inserts or data migrations.

• Disable foreign key checks:

  sql
  SET foreign_key_checks = 0;
  

• Enable foreign key checks:

  sql
  SET foreign_key_checks = 1;
  

6. Foreign Key Best Practices

• Use Descriptive Names: Name constraints for easier management.
• Avoid Cascading Deletes in critical tables to prevent accidental data loss.
• Ensure Data Consistency: Insert data in the parent table before inserting in the child table to avoid foreign key violations.
• Indexes on Foreign Keys: In many databases, it’s recommended to index foreign key columns for better query performance.

7. Common Errors and Troubleshooting

• Error: "Cannot add or update a child row: a foreign key constraint fails."

  • Cause: This occurs if you attempt to add a foreign key value in the child table that doesn’t exist in the parent table.
  • Solution: Ensure that the referenced key exists in the parent table.

• Error: "Cannot delete or update a parent row: a foreign key constraint fails."

  • Cause: This occurs if you attempt to delete or update a row in the parent table that has dependent rows in the child table.
  • Solution: Use ON DELETE CASCADE or manually delete the dependent rows first.

8. Advanced Example: Multiple Foreign Keys

You can create tables with multiple foreign keys, referencing different tables.

sql
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE SET NULL
);

In this example:

• order_id references orders, and if an order is deleted, the related order_items entries are also deleted.
• product_id references products, and if a product is deleted, product_id is set to NULL in order_items.

Get the list of tables in your database:

1)  SHOW TABLES;


2)  SHOW CREATE TABLE table_name;



ALTER TABLE group_members 
ADD CONSTRAINT fk_group_id
FOREIGN KEY (group_id) 
REFERENCES call_groups(group_id)
ON DELETE CASCADE
ON UPDATE CASCADE;



Summary

• Foreign keys maintain relationships and referential integrity between tables.
• Cascade options (ON DELETE, ON UPDATE) allow flexibility in handling parent-child table relationships.
• Naming constraints and indexing foreign key columns can improve readability and performance.
• Disable foreign key checks temporarily when performing bulk operations if necessary.

With this guide, you should be well-equipped to define and manage foreign keys effectively in SQL databases!