SQLite Foreign Keys and ER Diagrams

Declare a reference

The orders table below references customers. Its customer_id is required and must refer to an existing customer when foreign key enforcement is enabled.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL
    REFERENCES customers(id),
  total REAL
);

Inspect the declared keys

SQLite exposes foreign key metadata through a PRAGMA. Composite foreign keys have multiple rows sharing an id. The diagram groups these into one relationship.

PRAGMA foreign_key_list("orders");

Distinguish declarations from valid data

A database may have been populated with enforcement disabled. A diagram shows the declared relationship; it does not prove the data is consistent. Run a foreign key check to find violations. SQLite Lab enables foreign key enforcement for normal workspace operations.

PRAGMA foreign_key_check;

Read an automatically generated diagram

Open the ER diagram tab and select the tables you need. Primary keys and foreign key columns are marked; reference lines connect tables. The current diagram does not infer precise business cardinalities or validate existing data. Check the Schema tab for nullability and uniqueness details.

When no lines appear

CSV imports do not automatically declare foreign keys. Tables with similarly named columns will remain disconnected until the database schema declares a relationship. SQLite does not support adding a foreign key through a simple ADD CONSTRAINT; rebuilding the table may be necessary.