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

Adding a Foreign Key to an Existing SQLite Table

Challenge:

You're working with an existing SQLite table, and you need to add a foreign key constraint on a particular column.

Solution:

Unlike other SQL implementations, SQLite does not support the ADD CONSTRAINT variant of the ALTER TABLE command. Therefore, it's not possible to directly add a foreign key constraint to an existing table.

Alternative Approach:

To achieve the desired result, you must follow these steps:

1. Save the data from the existing table into a temporary table.
2. Drop the existing table.
3. Recreate the table with the foreign key constraint included.
4. Insert the data back from the temporary table into the new table.

Example SQL for this process:

-- Step 1: Create a temporary table
CREATE TEMPORARY TABLE t_child AS SELECT * FROM child;

-- Step 2: Drop the existing table
DROP TABLE child;

-- Step 3: Recreate the table with the foreign key constraint
CREATE TABLE child (
    id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    description TEXT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

-- Step 4: Insert data back from the temporary table
INSERT INTO child SELECT * FROM t_child;

By executing these steps, you essentially create a new table with the desired foreign key constraint while preserving the data from the original table.

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