Skip to content
Coordinator DB
View as Markdown
View as Markdown

Design a schema ​

A schema states the values and relationships the application accepts. Begin with stable identifiers, required fields and the numeric or structured contracts those fields need.

Enable foreign-key enforcement when the application relies on references:

sql
PRAGMA foreign_keys = ON;
CREATE TABLE customers(id INTEGER PRIMARY KEY, name String NOT NULL);
CREATE TABLE orders
(
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    amount Decimal(18, 2) CHECK (amount >= 0)
);
INSERT INTO customers VALUES (1, 'Ada');
INSERT INTO orders VALUES (10, 1, '12.50');

The name and customer reference are required. The amount is an exact decimal and, when present, must be nonnegative. Add NOT NULL to amount too if missing amounts are invalid for the application.

sql
SELECT orders.id, customers.name, orders.amount
FROM orders
JOIN customers ON customers.id = orders.customer_id
FORMAT csv;
text
10,"Ada",12.50

Index for actual queries ​

Use primary and unique keys to enforce identity, and other indexes for concrete filter and ordering patterns. Verify the plan with EXPLAIN QUERY PLAN rather than assuming an index is selected.

See data types, CREATE TABLE and CREATE INDEX.