Foreign key constraints

To check whether your current version of SQLite supports foreign key constraints or not, you use the following command.

PRAGMA foreign_keys;

The command returns an integer value: 1: enable, 0: disabled. If the command returns nothing, it means that your SQLite version doesn’t support foreign key constraints.

PRAGMA foreign_keys = OFF;
PRAGMA foreign_keys = ON;
CREATE TABLE suppliers (
    supplier_id   INTEGER PRIMARY KEY,
    supplier_name TEXT    NOT NULL,
    group_id      INTEGER NOT NULL,
    FOREIGN KEY (group_id)
       REFERENCES supplier_groups (group_id) 
);

The supplier_groups table is called a parent table, which is the table that a foreign key references. The suppliers table is known as a child table, which is the table to which the foreign key constraint applies.

The group_id column in the supplier_groups table is called the parent key, which is a column or a set of columns in the parent table that the foreign key constraint references. Typically, the parent key is the primary key of the parent table.

The group_id column in the suppliers table is called the child key. Generally, the child key references to the primary key of the parent table.

INSERT INTO suppliers (supplier_name, group_id)
VALUES ('HP', 2);

foreign key constraint actions

To specify how foreign key constraint behaves whenever the parent key is deleted or updated, you use the ON DELETE or ON UPDATE action as follows:

FOREIGN KEY (foreign_key_columns)
   REFERENCES parent_table(parent_key_columns)
      ON UPDATE action 
      ON DELETE action;

SQLite supports the following actions:

CREATE TABLE suppliers (
    supplier_id   INTEGER PRIMARY KEY,
    supplier_name TEXT    NOT NULL,
    group_id      INTEGER,
    FOREIGN KEY (group_id)
    REFERENCES supplier_groups (group_id) 
       ON UPDATE CASCADE
       ON DELETE CASCADE
);