How Can I Add a Foreign Key Constraint to an Existing SQLite Table?

SQLite: Adding Foreign Key to Existing Table

When querying a database, maintaining data integrity is paramount. Foreign keys play a crucial role in enforcing relationships between tables, ensuring that data references are valid. In the context of SQLite, however, certain limitations exist when it comes to modifying existing tables.

Challenge: Adding Foreign Key to Existing Table

Consider the following scenario: You have an existing SQLite table named "child" with the following schema:

CREATE TABLE child( 
  id INTEGER PRIMARY KEY, 
  parent_id INTEGER, 
  description TEXT);

Your objective is to establish a foreign key constraint on the "parent_id" column, assuming that foreign keys are already enabled. However, conventional methods may not suffice.

Solution: Limitations and Workaround

In SQLite, adding a foreign key to an existing table is not directly possible using the "ALTER TABLE ADD CONSTRAINT" syntax. Unlike other database systems, SQLite lacks support for this specific ALTER TABLE variant.

The only feasible solution is to modify the table during its creation. You can drop the existing "child" table, recreate it with the foreign key constraint, and then repopulate it with the saved data from a temporary table:

CREATE TABLE child ( 
    id           INTEGER PRIMARY KEY, 
    parent_id    INTEGER, 
    description  TEXT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

This procedure ensures that the foreign key constraint is successfully established for the "child" table.

The above is the detailed content of How Can I Add a Foreign Key Constraint to an Existing SQLite Table?. For more information, please follow other related articles on the PHP Chinese website!