Lecture on Foreign Key Constraint

Introduction to Foreign Keys

• Foreign Key Definition: A primary key from one table that appears in a different table.
• Purpose: Establishes a link between two tables.

Benefits of Foreign Keys

1. Data Linking:
   • Enables referencing related information across tables.
   • Example: Customer ID in transactions can be used to find customer details in the customers table.
2. Data Integrity:
   • Prevents actions that would destroy the link between tables.

Creating Foreign Keys

• Example Tables:
  • Employees
  • Products
  • Transactions
  • Customers
• Customers Table Structure:
  • Primary Key: Customer ID
  • Other Fields: First Name, Last Name
• Linking Tables:
  • Create a link between Customers and Transactions via Customer ID.
  • Drop and recreate Transactions table to include foreign key constraint.

Syntax for Foreign Key Constraint

• Add a Foreign Key:

  ALTER TABLE Transactions ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

• Naming Foreign Key Constraints:

  • Use ADD CONSTRAINT to give a unique name.
  • Example: ALTER TABLE Transactions ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Managing Foreign Keys

• Viewing Active Foreign Keys:
  • Check under foreign keys section in table metadata.
• Dropping a Foreign Key: ALTER TABLE Transactions DROP FOREIGN KEY transactions_IBFK_1;

Practical Example

• Modify Auto Increment: ALTER TABLE Transactions AUTO_INCREMENT = 1000;
• Inserting Rows:
  • Insert transactions data with amounts and customer IDs.
• Data Integrity Check:
  • Attempting to delete a customer with active foreign key constraint fails.
  DELETE FROM Customers WHERE customerId = 3;
  • Error: Cannot delete or update a parent row due to foreign key constraint.

Conclusion

• Recap: Foreign key is a primary key from one table used in another.
• Next Steps: Further practice with joins integrating foreign keys.
• Final Note: Foreign key constraints maintain database integrity by preventing the deletion of referenced data unless the constraint is explicitly dropped.