Referential integrity is a fundamental concept in relational databases that ensures the consistency and accuracy of data. It involves maintaining the relationships between tables in a database to prevent orphaned records and ensure that data remains reliable and meaningful.

1. Primary Key (PK):

   • A primary key is a unique identifier for each record in a table.
   • It ensures that each record can be uniquely identified.
   • Example: In a Customers table, the CustomerID could be the primary key.

2. Foreign Key (FK):

   • A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
   • It establishes a link between the data in two tables.
   • Example: In an Orders table, the CustomerID could be a foreign key that references the CustomerID in the Customers table.

3. Referential Integrity Constraint:

   • A rule that ensures that foreign keys correctly and consistently reference primary keys.
   • It prevents actions that would leave orphaned records in the database.

• The Customers table has a primary key CustomerID.
• The Orders table has a foreign key CustomerID that references the CustomerID in the Customers table.
• This setup ensures that every order is linked to a valid customer.

1. Insert Operations:

   • When inserting a record into the Orders table, the CustomerID must exist in the Customers table.
   • Example:

     -- Valid insert
     INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
     VALUES (1, 'John Doe', 'John', 'USA');
     
     INSERT INTO Orders (OrderID, OrderDate, CustomerID)
     VALUES (101, '2023-10-01', 1);
     
     -- Invalid insert (CustomerID 2 does not exist in Customers table)
     INSERT INTO Orders (OrderID, OrderDate, CustomerID)
     VALUES (102, '2023-10-02', 2); -- This will fail
     

2. Update Operations:

   • When updating a foreign key, the new value must exist in the referenced table.
   • Example:

     -- Valid update
     UPDATE Orders
     SET CustomerID = 1
     WHERE OrderID = 101;
     
     -- Invalid update (CustomerID 2 does not exist in Customers table)
     UPDATE Orders
     SET CustomerID = 2
     WHERE OrderID = 101; -- This will fail
     

3. Delete Operations:

   • Deleting a record that is referenced by a foreign key can violate referential integrity.
   • To handle this, you can use ON DELETE and ON UPDATE actions:
     • CASCADE: Automatically delete or update the dependent records.
     • SET NULL: Set the foreign key to NULL.
     • RESTRICT: Prevent the delete or update operation.
   • Example:

     -- Creating the Orders table with ON DELETE CASCADE
     CREATE TABLE Orders (
         OrderID INT PRIMARY KEY,
         OrderDate DATE,
         CustomerID INT,
         FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
     );
     
     -- Deleting a customer will automatically delete related orders
     DELETE FROM Customers WHERE CustomerID = 1;
     

1. Create a Products table with the following fields:

   • ProductID (Primary Key)
   • ProductName
   • SupplierID (Foreign Key referencing Suppliers table)

2. Create a Suppliers table with the following fields:

   • SupplierID (Primary Key)
   • SupplierName
   • ContactName
   • Country

1. Insert a record into the Suppliers table.
2. Insert a record into the Products table with a valid SupplierID.
3. Attempt to insert a record into the Products table with an invalid SupplierID.

• Mistake: Forgetting to define foreign keys.
  • Tip: Always define foreign keys to maintain referential integrity.
• Mistake: Attempting to delete a record that is referenced by a foreign key without handling the constraint.
  • Tip: Use ON DELETE CASCADE or other actions to manage deletions.

Referential integrity is crucial for maintaining the consistency and reliability of data in relational databases. By using primary and foreign keys, and enforcing referential integrity constraints, you can ensure that your database remains accurate and meaningful. Understanding and implementing these concepts will help you design robust and reliable database systems.