Understanding database relationships is key to effective data modeling. In SQLite, we can define relationships between tables using foreign keys – a column that references the primary key of another table. This enforces data integrity and consistency within the database.
In this comprehensive guide, we will walk through foreign keys in SQLite while building out an example database step-by-step. You’ll learn how to create tables, define primary keys, set up foreign key constraints, and insert related data across multiple tables.
Defining foreign keys between tables is critical for enforcing relationships and data consistency in SQLite databases. The foreign key column in the child table matches the referenced primary key column from the parent table. Rows can then be insertedreuse primary keys across tables, while ON DELETE CASCADE rules propagate deletes from parent to child tables – avoiding orphan records. Overall, foreign keys help ensure integrity as applications access and manipulate related data across multiple tables.
Along the way, we’ll cover:
- The fundamentals of relational data modeling
- How foreign keys connect tables together
- Syntax for defining foreign key constraints in SQLite
- Techniques for inserting, updating, and deleting related data
- How foreign keys help maintain data integrity
- Options for cascading deletes and updates
Let’s get started!
Also read: SQLite Date Functions: A Comprehensive Guide
Relational Data Modeling Basics
Before diving into foreign keys, let’s quickly recap some key concepts in relational database design…
In a nutshell, relational databases store data in tabular format across multiple tables. Tables contain rows and columns similar to spreadsheets.
Key components include:
- Tables – Contains columns and rows that store data
- Columns – Defines details about the data
- Rows – Single record in the table
- Primary Key – Unique identifier for rows in a table
- Foreign Key – Column that links data between tables
Benefits of this approach:
- Structured organization of data
- Reduced data redundancy
- Enforced data integrity
- Flexible querying of related data
But how do we connect these tables together to build relationships? This is where foreign keys come in!
Foreign Keys Explained
A foreign key is a column that establishes a link between two tables. It acts as a cross-reference between tables by matching the value of one column (typically the primary key) in one table to another column in a different table.
For example, an orders table might have a customer_id column that references the customer_id primary key on the customers table. This connects orders to the customer that placed them.
The table containing the foreign key is called the child table. The table that contains the referenced column (usually the primary key) is called the parent table. This establishes a clear hierarchy between the two tables.
In practice, foreign keys enforce referential integrity constraints on relationships. This ensures:
- Child records always reference a valid parent record
- Deleting parent records cascades to child records
- Prevents orphan records and inconsistencies
Next let’s see how to implement foreign keys when creating SQLite tables…
Creating Tables with Foreign Keys in SQLite
We will model a simple e-commerce database with products, orders, and customers tables connected via foreign keys.
-- Parent table
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
name TEXT,
price NUMERIC
);
-- Child table
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
FOREIGN KEY(product_id) REFERENCES products(product_id)
);
-- Parent table
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT
);
-- Child table
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date TEXT,
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);
Let’s break this down…
We first created a products table with a primary key product_id column for uniquely identifying rows.
Next we defined an orders table with a product_id column intended to reference the products table.
We configured this foreign key relationship using:
FOREIGN KEY(product_id) REFERENCES products(product_id)
This ensures all product_id values in the orders table refers to a valid product record. This is our parent-child relationship between the tables.
Similarly, we created customers and orders tables with a foreign key defined from orders → customers linking related data.
Key Takeaways:
- Child tables contain foreign keys, parent tables contain referenced primary key
REFERENCESdefines table and column being referenced- Enforces data consistency between tables
Now let’s look at how we can work with related data across multiple tables…
Inserting Related Data Across Tables
With our schema in place, let’s add some records by inserting rows across the related products, orders, and customers tables.
-- Add products
INSERT INTO products (name, price) VALUES
('Product 1', 9.99),
('Product 2', 5.49),
('Product 3', 15.00);
-- List products (with generated product_id values)
SELECT * FROM products;
/*
product_id name price
---------- ---------- ----------
1 Product 1 9.99
2 Product 2 5.49
3 Product 3 15
*/
-- Add customers
INSERT INTO customers (first_name, last_name) VALUES
('John', 'Doe'),
('Lisa', 'Smith'),
('Ahmed', 'Khan');
-- List all customers
SELECT * FROM customers;
/*
customer_id first_name last_name
------------ ---------- --------------
1 John Doe
2 Lisa Smith
3 Ahmed Khan
*/
-- Add related orders data
INSERT INTO orders (product_id, customer_id, quantity) VALUES
(1, 1, 2),
(2, 1, 1),
(3, 2, 5),
(1, 3, 10);
-- List all orders
SELECT * FROM orders;
/*
order_id product_id customer_id quantity
---------- ---------- ----------- ----------
1 1 1 2
2 2 1 1
3 3 2 5
4 1 3 10
*/
The key steps we took:
- Inserted parent products records
- Inserted parent customer records
- Inserted related orders with foreign keys matching:
- Valid
product_idvalues from products table - Valid
customer_idvalues from customers table
- Valid
This allowed us to reuse existing keys between tables for maintaining relationships and data consistency.
Later if a product gets deleted – all child records using that product become invalid and also get deleted by default – avoiding orphan records!
ON DELETE CASCADE Overview
Speaking of deletes – what happens when parent records referenced by a foreign key gets deleted?
It will result in child records having invalid foreign key values causing data inconsistency.
To handle this, we can configure foreign keys to automatically:
- Restrict: Rejects delete preventing data loss
- Set Null: Sets foreign key as NULL
- Cascade: Deletes child records
ON DELETE CASCADE is the most common approach.
Below is the syntax for defining on delete rules when creating tables:
-- Example products and orders table
CREATE TABLE products (
product_id INTEGER PRIMARY KEY
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY
product_id INTEGER,
FOREIGN KEY(product_id)
REFERENCES products(product_id)
ON DELETE CASCADE
);
Now if a product gets deleted, all related orders also get deleted automatically avoiding inconsistencies!
ON DELETE CASCADE works similarly with UPDATE statements propagating changes from parent to child tables.
Summary Tables for Quick Reference
Here is a comparison table summarizing key details on foreign keys in SQLite covered so far:
| Topic | Description |
|---|---|
| Foreign Key | A column that references the primary key of another table |
| Child Table | Contains the foreign key |
| Parent Table | Contains referenced primary key column |
| REFERENCES | Defines parent table and referenced column |
| Inserts | Reuse keys between tables to preserve relationships |
| ON DELETE CASCADE | Deletes child records when parent key deleted |
In addition, refer to below code snippets showing foreign key syntax, creation, inserts, and deletes based on our e-commerce example database:
Foreign Key Definition
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
product_id INTEGER,
FOREIGN KEY(product_id) REFERENCES products(product_id)
);
Insert Related Data
INSERT INTO orders (product_id, customer_id)
VALUES (1, 2);
ON DELETE CASCADE
FOREIGN KEY(product_id)
REFERENCES products(product_id)
ON DELETE CASCADE
Putting it All Together
In this comprehensive deep dive, you learned all about foreign keys in SQLite – from relationships concepts to triggers and integrity constraints.
We took a very hands-on approach building out an example database schema step-by-step:
- Covered syntax for defining foreign key constraints
- Added multi-table data with foreign keys intact
- Discussed deletes cascades and updates for data consistency
You should now have a solid grasp of working with relational data across multiple linked tables powered by foreign keys in SQLite.
The structured organization and flexibility it provides will serve you well for any application working with related data!