70.806
Creating, Modifying, and Deleting Tables in SQLite

Introduction

Relational databases serve as the foundation for structured data storage, ensuring that data is consistently organized and efficiently accessible. In SQLite, as in other relational database systems, the creation, modification, and deletion of tables are fundamental operations that define the structure of the database schema.

In this lesson, you will learn about how to create, modify (alter), and remove (drop or delete) tables in SQLite, although the SQL syntax applies to all relational databases in general. Note that SQL is not case sensitive, but it is common practice to capitalize SQL keywords.

SQLite Databases

SQLite is a server-less database that stores its data in files or in an in-memory database. It is commonly used for mobile app and web development, as well as for research and proof-of-concept prototyping. It is not a multi-user database and thus is not recommended for business environments. In those situations, MySQL, Oracle, SQL Server, or a similar database is recommended. However, the standard nature of SQL means that the statements explained in this lesson are universally applicable to all relational databases.

Executing SQL in R

SQLite databases can be managed directly within R using the DBI and RSQLite packages. These tools allow users to execute SQL statements from R scripts or R Notebooks. R Notebooks, which are part of R Markdown, also support SQL code chunks, allowing users to write and execute SQL directly within a document.

SQL in R

R provides a programmatic way to execute SQL statements using the DBI package, which acts as a common interface for database connections, and RSQLite, which provides an implementation for SQLite.

Step 1: Install and Load Necessary Packages

If the required packages are not installed, they must be installed first. To install packages in R automatically but only if they are not already installed, you can check whether a package is available using the installed.packages() function. If the package is not installed, you can install it using install.packages().

The following R code ensures that required packages are installed before loading them:

# Define a vector of required packages
required_packages <- c("DBI", "RSQLite")

# Check if each package is installed, and install it if necessary
for (pkg in required_packages) {
  if (!pkg %in% installed.packages()[, "Package"]) {
    install.packages(pkg, repos = "https://cloud.r-project.org/")
  }
}

Once we are assured that the packages are installed in our local R environment, we need to load the packages into our R session using either the library() or the require() functions.

library(DBI)
library(RSQLite)

Step 2. Connect to Database

Creating a new database in SQLite is simple: just “connect” to a non-existing database. If the database file does not exist, it will create a new database file in your file system.

In R, the RSQLite package is required for connecting and working with SQLite databases. The dbConnect() function connects to a database; or creates it if it doesn’t exist. The R code fragment below creates a new database.

# load the necessary package
library(RSQLite)

# change this code to connect to your specific database
conn <- dbConnect(RSQLite::SQLite(), "healthcare.db")

As an aside, the special database “file name” “:memory:” will create a database in memory. An in-memory database is great for exploration, learning, proof-of-concept work, or quick experiments but it is not persistent – when the computer or applications shuts down, the data will disappear. When you need persistent data, use a file on disk.

Step 3: Execute a SQL Statement

Once you are connected to the database, you can execute SQL statements for queries or to create or modify tables and data using the dbExecute() function. For example, the code below illustrates how to create a Patients table using an R function. This approach will work for both R Scripts (programs) and in R Notebooks within an R code chunk:

status <- dbExecute(conn, "
    CREATE TABLE IF NOT EXISTS Patients (
        PatientID INTEGER PRIMARY KEY AUTOINCREMENT,
        FirstName TEXT NOT NULL,
        LastName TEXT NOT NULL,
        DateOfBirth DATE NOT NULL,
        Gender TEXT CHECK(Gender IN ('Male', 'Female', 'Other')),
        ContactNumber TEXT UNIQUE,
        Email TEXT UNIQUE,
        Address TEXT,
        EmergencyContact TEXT,
        InsuranceNumber TEXT UNIQUE,
        RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
")

This SQL statement embedded with an R function call creates the Patients table with constraints to ensure data integrity – we will explain the various parts of this table creation statement later in this lesson. Note the first argument to dbExecute() is the database connection object that was returned from the call to dbConnect().

Step 4: Verify Table Creation

It is good practice, to “test” the creation code by listing all tables in the database. This will confirm that the table was created. The DBI function dbListTables() will list the names of all tables present in the database referenced by conn.

dbListTables(conn)

If successful, the output will include “Patients”.

Step 5: Close Connection

Always disconnect from the database when done executing queries in order to free resources:

dbDisconnect(conn)

SQL Code Chunks

R Notebooks (a feature of R Markdown) allow embedding SQL queries directly using SQL chunks. These SQL chunks execute queries against a connected database without requiring explicit function calls in R. In fact, during knitting of the markdown, the SQL code chunks are translated to R function calls, so there is no disadvantage to using SQL code chunks.

Step 1: Creating an R Notebook with SQL Support

In RStudio, create an R Notebook (.Rmd file). Inside the notebook, add a setup chunk to establish a connection:

Step 2: Writing SQL Code in SQL Code Chunks

Within the R Notebook, create an SQL chunk to execute SQL statements. The chunk is specified using:

Note the code chunk parameter connection must be set to the database connection object returned from dbConnect().

A SQL code chunk can only contain one SQL statement, so to create multiple tables, you need multiple code chunks, one per SQL statement.

The semi-colon at the end of the SQL statement is customary, but neither required nor necessary as we cannot have multiple SQL statements. Hence, no statement separator is needed.

Step 3: Querying the Database in SQL Chunks

To retrieve data from the Patients table in an R Notebook, add another SQL chunk:

Table 1: 0 records

PatientID	FirstName	LastName	DateOfBirth	Gender	ContactNumber	Email	Address	EmergencyContact	InsuranceNumber	RegistrationDate

By default, the result of any query from a SQL code chunk is rendered as a table in the knitted document. However, it is possible to capture the query result set in a dataframe rather than having it displayed.

If you want to use code chunks rather than calling an R function such as dbGetQuery() to retrieve data, you can store the result of an SQL query into an R dataframe using the output.var option in a {sql} code chunk.

The output.var="patients_df" option stores the result of the SQL query into an R dataframe named patients_df.

Once executed, the dataframe patients_df will be available in the R environment and can be used like any other R dataframe:

# Display the first few rows
head(patients_df)

Of course, if you prefer working with standard R code chunks ({r}) instead of {sql} or you are building R Scripts rather than R Notebooks, you can capture SQL query results using the dbGetQuery() function from the DBI package.

# Retrieve data from Patients table
patients_df <- dbGetQuery(conn, "SELECT * FROM Patients")

# View the first few rows
head(patients_df)

SQL Chunks vs R Functions

Method	Pros	Cons
Using R Functions (dbExecute())	Full programmatic control, can use dynamically generated SQL	Requires writing SQL within R functions
Using SQL Code Chunks in R Notebooks	Cleaner SQL syntax, good for documentation, inline execution	Limited to predefined queries, less dynamic, only in R Notebooks

Creating Tables

The process of defining a database schema begins with the CREATE TABLE statement. This statement creates a table with specified attributes, each with a designated data type and, optionally, constraints such as primary keys, foreign keys, and uniqueness constraints.

Consider a database designed to manage patient records in a hospital setting.

To create a table to store patient information, the following SQL statement can be executed (assuming that the table does not yet exist, otherwise drop it first):

CREATE TABLE Patients (
    PatientID INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender TEXT CHECK(Gender IN ('Male', 'Female', 'Other')),
    ContactNumber TEXT UNIQUE,
    Email TEXT UNIQUE,
    Address TEXT,
    EmergencyContact TEXT,
    InsuranceNumber TEXT UNIQUE,
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This Patients table defines multiple attributes to store essential patient information. They are the “columns” in the table.

The PatientID serves as the primary key and is automatically incremented with each new record. Primary key values must be unique and the database will not allow any insertion of duplicate primary key values. Of course, rather than having the database provide a primary key value, you can provide your own value – just ommit the AUTOINCREMENT.

The constraints enforce data integrity by ensuring that critical fields such as FirstName, LastName, and DateOfBirth are non-null, i.e. cannot be left blank.

The Gender field is restricted to predefined values using the CHECK constraint mechanism, and contact details, including phone number, email, and insurance number, are unique to prevent duplicate records – these act as so-called alternate keys.

Additionally, the RegistrationDate column is set to store the current timestamp by default when a new record is inserted.

To store medical records associated with patients, another table can be created to maintain diagnosis details:

CREATE TABLE MedicalRecords (
    RecordID INTEGER PRIMARY KEY AUTOINCREMENT,
    PatientID INTEGER NOT NULL,
    Diagnosis TEXT NOT NULL,
    Treatment TEXT,
    PrescribedMedications TEXT,
    DateOfVisit TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) ON DELETE CASCADE
);

The MedicalRecords table establishes a foreign key relationship with the Patients table using the PatientID field. This ensures referential integrity by linking each medical record to a valid patient.

The ON DELETE CASCADE clause ensures that when a patient is deleted, their corresponding medical records are also removed from the database, preventing orphaned records.

Foreign Key Constraint Enforcement

Unlike other relational database management systems (RDBMS) such as MySQL, PostgreSQL, and SQL Server, SQLite does not enforce foreign key constraints by default. This design choice was originally made to maintain backward compatibility with earlier versions of SQLite, which did not support foreign key constraints at all. Additionally, not checking foreign key constraints improves data insertion and deletion performance. Enforcing foreign key constraints requires additional processing overhead, which can slightly impact performance, especially in embedded systems or lightweight applications where SQLite is commonly used.

Since SQLite aims to be fast, lightweight, and highly portable, foreign key enforcement was disabled by default to avoid breaking compatibility with older applications that were designed without expecting constraint enforcement.

For applications that require strict referential integrity, foreign key enforcement must be explicitly enabled using the PRAGMA foreign_keys = ON; statement.

Enabling FK Constraints in SQLite

To enforce foreign key constraints in SQLite, you must enable foreign key enforcement each time a database connection is opened. This is done using the PRAGMA foreign_keys = ON; statement.

When using SQLite from the command line or an SQL script, you can enable foreign key checking by running:

PRAGMA foreign_keys = ON;

After executing this statement, SQLite will enforce foreign key constraints for the duration of the session. This means that any attempt to insert, update, or delete a record that violates a foreign key constraint will result in an error.

For example, consider the following schema:

CREATE TABLE Patients (
    PatientID INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL
);

CREATE TABLE MedicalRecords (
    RecordID INTEGER PRIMARY KEY AUTOINCREMENT,
    PatientID INTEGER NOT NULL,
    Diagnosis TEXT NOT NULL,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
);

If PRAGMA foreign_keys = ON; is not set, you can insert a record into MedicalRecords referencing a non-existent PatientID, and SQLite will allow it:

INSERT INTO MedicalRecords (PatientID, Diagnosis) VALUES (999, 'Hypertension');

However, if foreign key enforcement is enabled, SQLite will reject the insert statement with an error:

Error: FOREIGN KEY constraint failed

Enabling Foreign Key Constraints in R

If you are working with SQLite in R using the DBI and RSQLite packages, you must explicitly enable foreign key support each time a connection is established.

# Load necessary libraries
library(DBI)
library(RSQLite)

# Establish a connection to the SQLite database
conn <- dbConnect(SQLite(), "bedDB.db")

# Enable foreign key constraint enforcement
dbExecute(conn, "PRAGMA foreign_keys = ON;")

## [1] 0

# Now execute SQL statements with foreign key constraints enforced

# When done with the database close the connection
dbDisconnect(conn)

Without setting PRAGMA foreign_keys = ON;, foreign key constraints will not be enforced in the R session, and invalid references to foreign keys will be allowed.

Enabling Foreign Key Constraints in a Persistent Way

SQLite does not allow foreign key enforcement to be set persistently across all sessions (unlike some database systems where foreign key constraints are always enforced once configured). The enforcement setting must be enabled every time a database connection is established.

However, in applications that repeatedly open and close database connections (e.g., web applications), a best practice is to execute PRAGMA foreign_keys = ON; immediately after opening a connection.

Verifying Whether Foreign Key Constraints Are Enabled

To check if foreign key enforcement is enabled in the current SQLite session, run:

PRAGMA foreign_keys;

If enforcement is enabled, SQLite will return:

1

If foreign key enforcement is disabled, it will return:

0

In short, SQLite does not enforce foreign key constraints by default to maintain backward compatibility and improve performance. However, if referential integrity is required, you must explicitly enable enforcement using PRAGMA foreign_keys = ON; every time a database connection is opened. In applications using SQLite, such as R and Python programs, ensuring that this PRAGMA statement is executed after establishing a connection is essential for maintaining database consistency.

Note that most other databases, such as MySQL, enforce referential integrity constraints by default unless you explicitly disable checking when necessary during bulk loading of data when checking would cause significant performance issues.

Existing Tables

If you attempt to create a table in that already exists, the database engine will return an error, preventing duplication. However, SQL provides a safeguard to handle such situations with the IF NOT EXISTS clause in the CREATE TABLE statement. This clause ensures that the table is only created if it does not already exist.

Consider the following example, where a hospital database is being designed to store patient records:

CREATE TABLE IF NOT EXISTS Patients (
    PatientID INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender TEXT CHECK(Gender IN ('Male', 'Female', 'Other')),
    ContactNumber TEXT UNIQUE,
    Email TEXT UNIQUE,
    Address TEXT,
    EmergencyContact TEXT,
    InsuranceNumber TEXT UNIQUE,
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this case, if the Patients table already exists, SQLite will not create a new one, preventing an error from occurring. Instead, the database will simply ignore the command.

If the IF NOT EXISTS clause is omitted and the table already exists, attempting to execute the following statement:

CREATE TABLE Patients (
    PatientID INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender TEXT CHECK(Gender IN ('Male', 'Female', 'Other')),
    ContactNumber TEXT UNIQUE,
    Email TEXT UNIQUE,
    Address TEXT,
    EmergencyContact TEXT,
    InsuranceNumber TEXT UNIQUE,
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

will result in an error similar to:

Error: table Patients already exists

This can be problematic in scenarios where automated processes attempt to create tables without prior knowledge of their existence.

Checking Table Existence

If greater control over the existence check is required, an alternative approach involves querying the SQLite sqlite_master system table. The sqlite_master table contains metadata about all database objects, including tables. To check for the existence of a specific table, the following SQL query can be used:

SELECT name FROM sqlite_master WHERE type='table' AND name='Patients';

If this query returns a result, it indicates that the Patients table already exists. This method can be incorporated into application logic or database scripts to determine whether a table should be created dynamically.

We recommend that when creating tables to use CREATE TABLE IF NOT EXISTS to prevent errors when defining tables that may already exist. For cases where more detailed checking is needed, querying sqlite_master can provide insight into the database schema. These strategies help maintain robustness in database design, especially in dynamic or automated environments such as healthcare information systems where schema definitions may be executed multiple times.

Altering Tables

Modifying an existing table is a frequent requirement as database schemas evolve. SQL provides the ALTER TABLE statement to facilitate such modifications.

Unlike more feature-rich database management systems such as PostgreSQL or MySQL, SQLite’s ALTER TABLE capabilities are limited to adding new columns, renaming existing columns, and renaming tables.

Suppose the hospital administration decides to track patients’ blood types and allergies. To accommodate this change, the schema can be updated using the following SQL statements:

ALTER TABLE Patients ADD COLUMN BloodType TEXT CHECK(BloodType IN ('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-'));
ALTER TABLE Patients ADD COLUMN Allergies TEXT;

These statements add the BloodType column with a CHECK constraint to restrict values to medically valid blood types and an Allergies column to store any known allergies.

Additionally, if there is a need to rename the MedicalRecords table to PatientMedicalRecords for clarity, this can be achieved with:

ALTER TABLE MedicalRecords RENAME TO PatientMedicalRecords;

Note that SQLite does not support directly dropping or modifying columns. If a column needs to be removed, the recommended approach is to create a new table with the desired schema, migrate existing data, and then rename the new table to match the original table’s name.

Deleting Tables

If a table is no longer needed, it can be permanently removed using the DROP TABLE statement. Suppose the hospital decides to discontinue maintaining certain old records, such as medical records that are no longer required for archival purposes. To remove the PatientMedicalRecords table entirely, the following SQL command can be executed:

DROP TABLE PatientMedicalRecords;

Executing this command irreversibly deletes the table and all its stored data. If the table has dependencies, such as foreign keys in other tables, the deletion may fail unless cascading constraints were predefined.

For cases where data needs to be removed while retaining the table structure, using the DELETE FROM statement without a WHERE clause is a preferable alternative:

DELETE FROM Patients;

This command removes all records from the Patients table while keeping its structure intact for future data insertion.

Database Scripts

In practice, database scripts are used to automate the process of defining a database schema, ensuring that tables, constraints, and relationships are established consistently. These scripts are typically written in SQL and executed as part of an application’s setup process or during database migrations.

A SQL script is simply a text file containing SQL commands separated by semi-colons.

For SQLite, database scripts are commonly stored in .sql text files and executed using the SQLite command-line interface (sqlite3), programmatically through a database connection in a language like Python, or within a database management tool.

Below is a sample SQL script (healthcare_schema.sql) designed to create a database schema for a hospital management system. It includes tables for storing patient information, medical records, and doctor details. The – is a comment that is ignored.

Contents of File healthcare_schema.sql

-- Enable foreign key constraints (important for SQLite)
PRAGMA foreign_keys = ON;

-- Create Patients table
CREATE TABLE IF NOT EXISTS Patients (
    PatientID INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender TEXT CHECK(Gender IN ('Male', 'Female', 'Other')),
    ContactNumber TEXT UNIQUE,
    Email TEXT UNIQUE,
    Address TEXT,
    EmergencyContact TEXT,
    InsuranceNumber TEXT UNIQUE,
    RegistrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Doctors table
CREATE TABLE IF NOT EXISTS Doctors (
    DoctorID INTEGER PRIMARY KEY AUTOINCREMENT,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Specialization TEXT NOT NULL,
    ContactNumber TEXT UNIQUE,
    Email TEXT UNIQUE
);

-- Create MedicalRecords table
CREATE TABLE IF NOT EXISTS MedicalRecords (
    RecordID INTEGER PRIMARY KEY AUTOINCREMENT,
    PatientID INTEGER NOT NULL,
    DoctorID INTEGER NOT NULL,
    Diagnosis TEXT NOT NULL,
    Treatment TEXT,
    PrescribedMedications TEXT,
    DateOfVisit TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (PatientID) REFERENCES Patients(PatientID) ON DELETE CASCADE,
    FOREIGN KEY (DoctorID) REFERENCES Doctors(DoctorID) ON DELETE SET NULL
);

This script ensures that tables are created only if they do not already exist. It also establishes referential integrity using foreign key constraints, ensuring that medical records reference valid patients and doctors.

There are multiple ways to execute this SQL script in SQLite. The most common methods include using the SQLite command-line tool or executing the script programmatically in Python or R.

Method 1: Running the Script Using the SQLite Command-Line Interface

1. Ensure SQLite is installed: If SQLite is not installed, download it from SQLite’s official website.

2. Open a terminal or command prompt.

3. Navigate to the script’s directory using the cd command.

4. Run the script with SQLite by executing:

   sqlite3 healthcare.db < healthcare_schema.sql

   This command will create a new SQLite database file (healthcare.db) and execute all the SQL statements within healthcare_schema.sql. If the database already exists, the script will ensure that the tables are created if they do not exist.

5. Verify the database schema by opening the SQLite shell and listing the tables:

   sqlite3 healthcare.db

   Then, within the SQLite shell, execute:

   .tables

   This should display Doctors, Patients, and MedicalRecords as the available tables.

Method 2: Executing the Script Programmatically

In many applications, database scripts are executed programmatically to initialize the database. The following Python script demonstrates how to execute healthcare_schema.sql using SQLite’s sqlite3 module.

import sqlite3

# Connect to SQLite database (creates file if it does not exist)
conn = sqlite3.connect("healthcare.db")
cursor = conn.cursor()

# Read and execute the SQL script
with open("healthcare_schema.sql", "r") as file:
    script = file.read()
    cursor.executescript(script)

# Commit and close connection
conn.commit()
conn.close()

print("Database schema created successfully.")

Running this Python script will execute all SQL statements within healthcare_schema.sql, setting up the database schema. This approach is commonly used in web applications, data pipelines, and system deployments where databases need to be initialized programmatically.

Here is an equivalent R script (not an R Notebook) to execute an SQLite database schema from a file, similar to the Python script provided earlier. In this example, we use the RSQLite package to connect to an SQLite database, read the SQL script, and execute it.

Before running the script, ensure that the DBI and RSQLite packages are installed, as shown earlier in this lesson.

# Load necessary libraries
library(DBI)
library(RSQLite)

# Define database file name
db_file <- "healthcare.db"

# Establish a connection to the SQLite database (creates file if not exists)
conn <- dbConnect(SQLite(), db_file)

# Read the SQL script file
sql_script <- readLines("healthcare_schema.sql")

# Collapse the script into a single string 
# (SQLite requires commands to be executed as complete statements)
sql_script_combined <- paste(sql_script, collapse = "\n")

# Execute the SQL script
dbExecute(conn, sql_script_combined)

# Close the database connection
dbDisconnect(conn)

# Confirm execution
cat("Database schema created successfully.\n")

Rather than executing an R Script from with R Studio (Posit), it can also be executed from the command line like a program. This allows database administration to be automated in shell scripts and jobs that are executed at specific times.

After running the script, you can check the created tables by executing the following commands in R:

# Reconnect to the database
conn <- dbConnect(SQLite(), "healthcare.db")

# List all tables in the database
dbListTables(conn)

# Disconnect
dbDisconnect(conn)

If successful, the output should display:

[1] "Doctors" "Patients" "MedicalRecords"

Deleting a SQLite Database

To delete the entire database, all of its tables, and all data, simply delete the file using your operating system’s file system manager. Alternatively, you can delete a database in R using the unlink() function.

# delete database in file patientDB.db
unlink("patientDB.db")

Summary

Understanding the fundamental operations of creating, altering, and deleting tables is crucial for structuring and maintaining relational databases effectively. SQLite provides essential mechanisms for defining tables, modifying schemas, and removing unnecessary structures while ensuring data integrity through constraints and foreign key relationships. By applying these principles to a healthcare database, it becomes possible to manage patient records, medical histories, and related information efficiently. As database requirements evolve, careful planning is necessary to ensure seamless modifications while preserving data consistency.

Executing SQL statements within R can be done using DBI functions or SQL code chunks in R Notebooks. Using dbExecute() within R scripts allows programmatic control, whereas SQL chunks in R Notebooks provide a more structured way to document and execute queries inline. Both methods support efficient interaction with SQLite databases, making them valuable tools for managing healthcare data or any other structured information.

Database scripts provide a structured and repeatable way to define and manage a relational database schema. In SQLite, scripts can be executed through the command-line interface or programmatically via database connectors in languages such as Python. Using scripts ensures that database schemas remain consistent across environments and deployments, particularly in applications such as healthcare management systems where data integrity is crucial.

---

Files & Resources

All Files for Lesson 70.806

---

References

SQLite AUTOINCREMENT. SQLite Tutorial.

SQLite AUTOINCREMENT. SQLite Documentation.

Errata

Let us know.