How do I add a foreign key to an existing SQLite table?

Adding foreign keys to existing tables in an SQLite database can be achieved through the following steps:

1. Determine the Foreign Key Requirements

Before adding a foreign key, it is essential to determine the requirements. Foreign keys establish relationships between two tables and ensure referential integrity. For example, if we have two tables: Customers and Orders, and each order should belong to a customer, we can add a foreign key in the Orders table referencing the primary key of the Customers table.

2. Using ALTER TABLE and Creating a New Table

As SQLite does not support adding foreign keys directly to existing tables using the ALTER TABLE command, an indirect approach is required:

Step A: Create a New Table

Create a new table with a structure similar to the original table but including foreign key constraints. For example, if you want to add a foreign key referencing the Customers table in the Orders table, the SQL command might be:

sql
CREATE TABLE new_Orders (
    OrderID int,
    OrderDate date,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Step B: Copy Data

Copy the data from the old table to the new table.

sql
INSERT INTO new_Orders (OrderID, OrderDate, CustomerID)
SELECT OrderID, OrderDate, CustomerID FROM Orders;

Step C: Drop the Old Table

sql
DROP TABLE Orders;

Step D: Rename the New Table

Rename the new table to the original table name.

sql
ALTER TABLE new_Orders RENAME TO Orders;

3. Verify Foreign Key Constraints

After adding the foreign key, verify that the foreign key constraints function as expected. You can attempt to insert data that violates the foreign key constraints to ensure SQLite rejects such insertions.

Example

Suppose we have a Customers table with CustomerID as the primary key. Now, we need to add a foreign key in the Orders table referencing CustomerID in Customers. After following the above steps, when you attempt to insert an order referencing a non-existent customer ID, SQLite should reject the insertion, confirming that the foreign key constraint is effective.

By this method, even though SQLite does not directly support adding foreign keys via the ALTER TABLE command, we can achieve it by rebuilding the table. This ensures data referential integrity and database integrity.