Relating

SQLite as a Relational Database

SQLite implements the relational model through its support for tables, relationships, and SQL standards. It stores data in tables with rows and columns, enforces data integrity through constraints, and supports transactions with ACID properties.

Example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

Core Principles of Relational Databases

The relational model is built on three fundamental principles: structure, integrity, and manipulation. Data is organized in relations (tables), with each relation containing tuples (rows) and attributes (columns). The model ensures data consistency through constraints and allows data manipulation through relational algebra.

Example:

-- Structure: Creating a well-defined table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    category TEXT
);

Purpose of Database Relationships

Relationships in databases prevent data redundancy, maintain consistency, and enable efficient data organization. They allow you to split related data into separate tables while maintaining connections between them.

Example:

-- Without relationships (poor design with redundancy)
CREATE TABLE orders_bad (
    id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_address TEXT,
    product_name TEXT,
    product_price REAL
);

-- With relationships (better design)
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    address TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Types of Database Relationships

One-to-One: Each record in Table A corresponds to exactly one record in Table B.
One-to-Many: One record in Table A can relate to multiple records in Table B.
Many-to-Many: Multiple records in Table A can relate to multiple records in Table B.

Examples:

-- One-to-One: User and Profile
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT
);

CREATE TABLE profiles (
    user_id INTEGER PRIMARY KEY,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- One-to-Many: Author and Books
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Many-to-Many: Students and Courses
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Choosing Relationship Types

Choose relationship types based on your data model requirements:

• Use one-to-one for extending table information without adding columns
• Use one-to-many for parent-child relationships
• Use many-to-many when elements in both tables need multiple associations

Primary Keys vs Foreign Keys

Primary keys uniquely identify records within a table, while foreign keys establish relationships between tables by referencing primary keys in other tables.

Example:

CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,  -- Primary Key
    name TEXT
);

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,   -- Primary Key
    name TEXT,
    dept_id INTEGER,             -- Foreign Key
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Selecting Appropriate Keys

Choose keys based on these principles:

• Primary keys should be unique and not null
• Use INTEGER PRIMARY KEY for automatic incrementing
• Consider composite keys when multiple columns uniquely identify records
• Foreign keys should match the data type of referenced primary keys

Example:

-- Using natural key
CREATE TABLE countries (
    country_code TEXT PRIMARY KEY,
    name TEXT
);

-- Using surrogate key
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_date TEXT,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Using composite key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Referential Integrity

Referential integrity ensures that relationships between tables in a database remain accurate and consistent. It guarantees that when a foreign key is used, it must reference a valid primary key in the parent table. This prevents orphaned records and maintains data reliability across database relationships.

Benefits:

• Improved data quality and accuracy
• Prevention of invalid relationships
• Enhanced application reliability
• Automatic cascading updates and deletes

Reading Entity Relationship Diagrams

An ERD illustrates how entities relate to each other within a system using standardized symbols and connectors. The diagram should be read from left to right and then right to left, following relationship lines between entities to understand their connections.

ERD Symbols and Notation

Core Components:

• Rectangles represent entities (tables)
• Ovals represent attributes
• Diamonds show relationships
• Lines connect related elements

Relationship Notations:

• One-to-One: Straight line with single markers
• One-to-Many: Crow’s foot on the “many” side
• Many-to-Many: Crow’s foot on both sides

Subqueries vs. Joins

Joins combine records from multiple tables based on related columns, while subqueries are nested queries within another query.

Performance Considerations:

• Joins are generally faster for large datasets
• Joins can use indexes more effectively
• Subqueries offer more flexibility for complex conditions

UNION vs. JOIN

• UNION combines rows vertically from similar tables
• JOIN combines columns horizontally from related tables
• Use UNION when combining similar data from separate tables
• Use JOIN when relating different data from multiple tables

WHERE vs. HAVING

Key Differences:

• WHERE filters individual rows before grouping
• HAVING filters groups after aggregation
• WHERE works with individual columns
• HAVING works with aggregate functions

Example:

-- WHERE example
SELECT department, salary 
FROM employees 
WHERE salary > 50000;

-- HAVING example
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

Choosing Between JOIN Types

Inner Join: Use when you want only matching records from both tables
Left Join: Use when you need all records from the left table and matching records from the right
Right Join: Use when you need all records from the right table and matching records from the left
Full Join: Use when you need all records from both tables, including non-matches

-- Inner Join example
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

-- Left Join example
SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Set Operations in SQLite

Set operations combine the results of two or more SELECT statements. The main operations are UNION, INTERSECT, and EXCEPT. Each operation treats SQL queries as sets of rows, allowing you to combine or compare query results.

-- UNION: Combines results and removes duplicates
SELECT name FROM employees
UNION
SELECT name FROM contractors;

-- UNION ALL: Combines results keeping duplicates
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

-- INTERSECT: Returns only rows present in both results
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

-- EXCEPT: Returns rows from first query not in second
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

Creating Table Relationships in SQLite

Table relationships are established using foreign keys that reference primary keys in other tables. First, enable foreign key support, then define the relationships in your table schema.

-- Enable foreign key support
PRAGMA foreign_keys = ON;

-- Create parent table
CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL
);

-- Create child table with foreign key
CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

INNER JOIN vs LEFT JOIN in SQLite

INNER JOIN returns only matching rows between tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table.

-- INNER JOIN: Only matching rows
SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

-- LEFT JOIN: All employees, even without departments
SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;

Implementing Many-to-Many Relationships

Many-to-many relationships require a junction table (also called bridge or linking table) that contains foreign keys to both related tables.

-- Create the main tables
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- Create the junction table
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Foreign Key Mismatch Errors

Foreign key mismatch errors occur when:

• Data types don’t match between foreign and referenced keys
• Referenced primary key doesn’t exist
• Foreign key constraints are violated

-- Common error scenario
INSERT INTO employees (emp_id, name, dept_id)
VALUES (1, 'John Doe', 999); -- Error if dept_id 999 doesn't exist

-- Proper insertion
INSERT INTO departments (dept_id, dept_name)
VALUES (1, 'IT');

INSERT INTO employees (emp_id, name, dept_id)
VALUES (1, 'John Doe', 1); -- Works correctly

Enabling Foreign Key Constraints

Foreign key constraints must be explicitly enabled in SQLite. They can be enabled per-connection or in the sqlite3 command line.

-- Enable foreign keys
PRAGMA foreign_keys = ON;

-- Verify status
PRAGMA foreign_keys;

-- Check for foreign key violations
PRAGMA foreign_key_check;

Parent-Child Relationships in SQLite

Parent-child relationships are implemented using foreign keys with optional ON DELETE and ON UPDATE clauses to specify referential actions.

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id) 
    REFERENCES categories(category_id)
    ON DELETE CASCADE  -- Automatically delete child records
    ON UPDATE CASCADE  -- Automatically update child records
);

Self-Referential Relationships in SQLite

Self-referential relationships allow a table to reference itself, commonly used for hierarchical data structures like employee-manager relationships or category trees. To implement this, create a foreign key that references the primary key within the same table.

Example: Employee Hierarchy

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- Insert sample data
INSERT INTO employees VALUES (1, 'John Doe', NULL);  -- CEO
INSERT INTO employees VALUES (2, 'Jane Smith', 1);   -- Reports to John
INSERT INTO employees VALUES (3, 'Bob Wilson', 2);   -- Reports to Jane

Cascade Delete in SQLite

Cascade deletion automatically removes related records when a parent record is deleted. This maintains referential integrity and prevents orphaned records.

Configuration

CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL
);

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) 
    REFERENCES departments(dept_id)
    ON DELETE CASCADE
);

Composite Foreign Keys in SQLite

Composite foreign keys reference multiple columns in combination to establish a relationship between tables. This is useful when the primary key of the referenced table consists of multiple columns.

Example: Order System

CREATE TABLE shipments (
    shipment_id INTEGER,
    location_id INTEGER,
    date DATE,
    PRIMARY KEY (shipment_id, location_id)
);

CREATE TABLE shipment_items (
    item_id INTEGER PRIMARY KEY,
    shipment_id INTEGER,
    location_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (shipment_id, location_id) 
    REFERENCES shipments(shipment_id, location_id)
);

Efficient Multi-Table Queries

Querying across multiple related tables requires careful consideration of join types and indexing strategies. The key is to use appropriate joins and optimize the query execution plan.

Example: Optimized Multi-Table Query

-- Create indexes for better performance
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- Efficient query using proper joins
SELECT 
    c.name,
    o.order_date,
    oi.product_name,
    oi.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= date('now', '-30 days');

Common Pitfalls in Table Relationships

Incorrect Foreign Key Definition

• Not enabling foreign key constraints
• Mismatching data types between primary and foreign keys
• Missing indexes on foreign key columns

Poor Schema Design

-- Bad: Denormalized design
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_address TEXT
);

-- Better: Normalized design
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    address TEXT
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Performance Considerations

• Create indexes on frequently joined columns
• Use appropriate join types based on data relationships
• Consider the impact of cascading operations on large datasets

Modifying tables with existing relationships in SQLite

When you need to change the structure of a table that is tied to one or more related tables, careful planning ensures that the constraints, foreign keys, and existing data remain consistent. In SQLite, you can add columns or rename a table directly using ALTER TABLE, but certain changes—such as dropping columns or altering column definitions—often require creating a new table and transferring the data.

Considerations

• Foreign key dependencies: Before making large structural changes, check if other tables reference the one you plan to modify. Any mismatch in data types or constraints can cause errors.
• Back up data: To avoid accidental data loss, create a backup of your tables or your entire database.
• Use transitional tables if needed: When a straightforward ALTER TABLE command is insufficient (for example, to drop a column), you can create a new table with the desired schema, copy the data over, then remove the old table.
• Update foreign key constraints: If a primary key or column that is part of a foreign key relationship changes, you must update the referencing foreign key definitions accordingly.

Example
Below is a pattern for renaming a column in SQLite prior to version 3.25, where the RENAME COLUMN feature did not exist:

-- 1. Create a new table with the correct schema
CREATE TABLE new_users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

-- 2. Transfer data from old table to new table
INSERT INTO new_users (user_id, username, email)
SELECT user_id, old_username, old_email
FROM users;

-- 3. Drop the old table
DROP TABLE users;

-- 4. Rename the new table to the old table’s name
ALTER TABLE new_users RENAME TO users;

If the users table is the target of a foreign key reference elsewhere, ensure that the references still match the new schema and that the primary key remains unchanged or is updated accordingly.

Implementing polymorphic relationships in SQLite

A polymorphic relationship allows a table to reference multiple other tables through a single foreign key with an additional “type” or “context” column. SQLite does not natively support polymorphic foreign keys, but you can implement a pattern that associates a record with different tables based on extra metadata.

Considerations

• Type or context field: Include a column that indicates which table the foreign key should reference.
• No strict referential enforcement: Because SQLite cannot enforce a foreign key constraint that dynamically refers to different tables, you must handle consistency in your application logic.
• Design trade-offs: Polymorphic relationships can reduce the number of tables and relationships you need, but they increase application complexity since you must handle multiple reference paths manually.

Example
Imagine a table called comments, which may be attached to either a posts table or a photos table:

CREATE TABLE comments (
    comment_id INTEGER PRIMARY KEY,
    content TEXT NOT NULL,
    parent_id INTEGER,        -- ID of the parent item
    parent_type TEXT NOT NULL -- "post" or "photo"
);

When you insert a new comment, you specify both parent_id and parent_type:

INSERT INTO comments (content, parent_id, parent_type)
VALUES ('Nice post!', 42, 'post');

INSERT INTO comments (content, parent_id, parent_type)
VALUES ('Great photo!', 100, 'photo');

Your application logic determines whether to look up parent_id in posts or photos based on the value in parent_type.

Handling NULL values in relationship columns in SQLite

In SQLite, a relationship column (foreign key) can be NULL if the database schema and constraints allow it. This makes sense when a related record is optional, but you must carefully analyze the implications of having missing references.

Considerations

• Optional relationships: A NULL foreign key indicates no link to another record. If that makes sense in your data model, set the foreign key column to allow NULL.
• NOT NULL constraint: Enforcing NOT NULL on a foreign key column ensures that a record must have a valid reference to another table.
• Referential integrity: Even if the foreign key can be NULL, ensure that your application checks for valid references when the column is not NULL.

Example
Below is an example where a user profile may not have a corresponding avatar in the avatars table, so the avatar_id column is allowed to be NULL:

CREATE TABLE user_profiles (
    profile_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    avatar_id INTEGER,
    FOREIGN KEY (avatar_id) REFERENCES avatars(avatar_id)
);

If avatar_id remains NULL, the user has no avatar. If it is not NULL, it must match a valid avatar_id in avatars. This makes the relationship optional and flexible.

Impact of relationships on SQLite performance

Establishing relationships in SQLite can improve data consistency and organization, but there may be performance impacts depending on how they are designed and maintained.

Considerations

• Foreign key checks: When foreign keys are enabled, every insert, update, or delete on a related table triggers checks that can impact write performance.
• Index usage: Proper indexing on columns used in foreign keys and join operations can significantly boost query performance and offset the overhead of foreign key checks.
• Join complexity: Complex queries involving many joined tables can slow down lookups if those queries are not optimized or if columns are not properly indexed.
• Data normalization: While normalization reduces data duplication, it can increase the number of join operations. Striking a balance between normalization and performance is essential.

Example
When retrieving data from multiple related tables, ensuring your join columns are indexed is critical:

CREATE INDEX idx_users_country_id ON users(country_id);

SELECT u.username, c.country_name
FROM users u
JOIN countries c ON u.country_id = c.country_id
WHERE c.country_name LIKE 'C%';

With the index, SQLite can quickly locate rows matching a particular country_id, reducing the time spent on lookups during join operations.