SQLite Constraints

13 Oct 2023

SQLite is a popular relational database management system that provides efficient storage and retrieval of data. Constraints in SQLite are rules that are applied to the data stored in the tables to ensure data integrity and enforce business logic. In this blog post, we will explore the different types of constraints available in SQLite and how to use them effectively in your database schema.

NOT NULL Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
CHECK Constraint
FOREIGN KEY Constraint

NOT NULL Constraint

The NOT NULL constraint is used to ensure that a column does not contain any NULL values. It specifies that the column must have a value when a new row is inserted into the table or when an existing row is updated.

To add the NOT NULL constraint to a column, you can use the following syntax:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

In the example above, the name column is defined with the NOT NULL constraint. This means that every row inserted into the employees table must have a non-null value for the name column.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are unique. It prevents duplicate values from being inserted into the column.

To add the UNIQUE constraint to a column, you can use the following syntax:

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    student_number TEXT UNIQUE,
    name TEXT
);

In the example above, the student_number column is defined with the UNIQUE constraint. This means that each row inserted into the students table must have a unique value for the student_number column.

PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify each row in a table. It can consist of one or more columns, and its values must be unique and not null.

To add the PRIMARY KEY constraint to a column, you can use the following syntax:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_name TEXT,
    quantity INTEGER
);

In the example above, the order_id column is defined as the PRIMARY KEY of the orders table. This means that each row in the orders table will have a unique and non-null value for the order_id column.

CHECK Constraint

The CHECK constraint is used to specify a condition that must be true for a row to be inserted or updated in a table.

To add the CHECK constraint to a column, you can use the following syntax:

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT,
    year INTEGER CHECK (year >= 1900)
);

In the example above, the year column is defined with the CHECK constraint, which ensures that the year value is greater than or equal to 1900.

FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to enforce referential integrity between two related tables. It ensures that the values in a column of one table match the values in another table’s primary key or unique key column.

To add the FOREIGN KEY constraint to a column, you can use the following syntax:

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

In the example above, the customer_id column in the orders table is defined as a FOREIGN KEY that references the id column in the customers table. This enforces the relationship between the orders and customers tables, ensuring that an order can only be associated with a valid customer.

Conclusion

SQLite constraints play a vital role in maintaining the consistency and integrity of the data stored in a database. By applying constraints, you can prevent invalid or inconsistent data from being inserted or updated in your tables. Understanding the different types of constraints available in SQLite and how to use them correctly can greatly enhance the reliability and usability of your database.

References:
SQLite Documentation
SQLite Constraints - Tutorialspoint

#SQLite #database

Table of Contents