Understanding SQLite Data Types and Constraints

SQLite is a lightweight, disk-based database that doesn't require a separate server process, making it one of the most popular database technologies for embedded systems and mobile applications. In this article, we'll explore the various data types and constraints SQLite uses to handle data more efficiently.

SQLite Data Types
Type Affinity
Constraints in SQLite
Conclusion

SQLite Data Types

SQLite uses dynamic types. This means the value of a certain column can store any data type. However, it does have the following storage classes to keep things organized:

NULL: Represents a missing value.
INTEGER: Stores whole numbers, positive and negative.
REAL: For floating-point numbers, using 8-byte IEEE floating-point numbers.
TEXT: Stores text strings.
BLOB: Any kind of data, stored exactly as it was input.

Here’s an example of how you might define a table using some of these storage classes:

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    grade REAL
);

Type Affinity

While SQLite is flexible with its data types, it employs the concept of type affinity, which means each column has an associated type affinity that directs SQLite about what data types best fit in that column. The affinivity may be:

TEXT: Columns with TEXT affinity prefer storing text.
NUMERIC: These columns try to convert data into a numeric storage class, saving numbers as INTEGER or REAL when possible.
INTEGER: Best suited for whole numbers.
REAL: Great for floating-point numbers.
BLOB: No preference, stores them exactly as input.

Constraints in SQLite

Constraints define rules regarding data integrity and uniqueness. SQLite offers several constraints that can be used to ensure that data inserted into a database conforms to specific rules.

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. When this constraint is assigned, it implicitly treats the column's data as UNIQUE and NOT NULL. Here’s SQL code that applies a PRIMARY KEY constraint:

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

UNIQUE Constraint

This constraint ensures all values in a column are distinct. If applied to a column, it means SQLite will reject duplicate entries in that column:

CREATE TABLE students (
    email TEXT UNIQUE
);

NOT NULL Constraint

The NOT NULL constraint enforces a column to accept only non-null values. This is useful for mandatory fields:

CREATE TABLE teachers (
    teacher_id INTEGER PRIMARY KEY,
    teacher_name TEXT NOT NULL
);

CHECK Constraint

CHECK constraints allow validations that data must satisfy whenever a row is inserted or updated within an SQLite table. Here's how you can set a CHECK constraint on a column:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    price REAL CHECK(price > 0)
);

FOREIGN KEY Constraint

FOREIGN KEYs ensure relationship between tables by making reference to a primary key in another table:

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

Conclusion

Understanding SQLite’s data types and constraints is vital in designing a robust database schema. By leveraging appropriate storage classes and constraints, you can enforce data integrity and relationships across tables efficiently. This forms a foundation for any scalable application, whether it runs embedded on a device or drives the backend of a complex mobile application.

Next Article: Mastering Dynamic Typing in SQLite

Series: SQLite Data Types and Constraints

SQLite