How to use foreign key constraints in SQLite?

10 months ago

William Carter

1 minute

In SQLite, using foreign key constraints requires defining the foreign key relationship when creating the table. Here is an example:

First, create two tables and define a foreign key constraint to use one table’s column as the foreign key for the other table.

CREATE TABLE table1 (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE table2 (
    id INTEGER PRIMARY KEY,
    table1_id INTEGER,
    FOREIGN KEY (table1_id) REFERENCES table1(id)
);

In the example above, the ‘table1_id’ column in table2 references the ‘id’ column of table1 as a foreign key.

When inserting data, it is important to make sure that foreign key constraints are not violated.

INSERT INTO table1 (id, name) VALUES (1, 'John');
INSERT INTO table2 (id, table1_id) VALUES (1, 1); // 这里的1是table1表中的id

Attempting to insert a non-existent foreign key value will result in an error message.

INSERT INTO table2 (id, table1_id) VALUES (2, 2); // 尝试插入一个不存在的外键值
-- Error: foreign key mismatch - "table2" referencing "table1"

This way, you can use foreign key constraints in SQLite.