DbSchema | How to Implement Constraints in Sqlite?

Sqlite • 04-Jun-2023

by Malik Umair Sajjad

SQLite alt >

Introduction
Prerequisites
What is a Constraint?
Advantages and Limitations of Constraints
SQLite Constraints
Implementing Constraints in sqlite3
Implementing Constraints in DbSchema
Conclusion
References

Introduction

SQLite is an open-source relational database system that uses the SQL (Structured Query Language) for querying. One of the critical aspects of SQL and thus SQLite is the use of constraints. Constraints are rules applied to columns in a database table to limit the type, and the range of values that can be used within columns, ensuring the integrity and consistency of the data within the table.

Prerequisites

This article assumes you have a basic understanding of SQL and database management systems. It would also be beneficial to have a fundamental understanding of SQLite and DbSchema.

For installation and establishing connection you can read our article SQLite-How to create a database?

What is a Constraint?

A constraint is a rule that’s applied to a column or set of columns in a database table with the goal of preserving the data integrity. Constraints enforce limits on the data type and the range of values that can be used within columns. If any action violates a constraint, that action is aborted.

Advantages and Limitations of Constraints

Advantages

Data Accuracy: Constraints ensure that the data adheres to the defined rules, maintaining data accuracy.
Data Consistency: By enforcing rules, constraints ensure the consistency of data across the database.
Preventing Invalid Data Entry: Constraints prevent invalid data from being entered into the database.

Limitations

Performance Impact: Constraints can impact database performance, especially when working with large data sets, as each data modification requires constraint checks.
Increased Complexity: They can increase the complexity of SQL queries and database design.
Potential for Data Entry Blockage: If not properly defined, constraints can become overly restrictive and block valid data entry.

Restrictions on Using a Constraint

There can be some restrictions on using constraints, like SQLite does not enforce the foreign key constraints by default, and you need to enable it manually.

SQLite Constraints alt >

SQLite Constraints

SQLite supports following constraints:

*Constraint*	_Description_
NOT NULL	Ensures that a column cannot have NULL value
DEFAULT	Provides a default value for a column when none is specified
UNIQUE	Ensures that all values in a column are unique
PRIMARY KEY	A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
CHECK	Ensures that all values in a column satisfies certain conditions

Implementing Constraints in sqlite3

Implementing constraints in SQLite3 involves creating or modifying a table structure using SQL commands within the SQLite3 shell.

Accessing sqlite3

SQLite3 is a terminal-based frontend to the SQLite library. You can start sqlite3 by simply typing “sqlite3” in your terminal:

sqlite3

Creating a new SQLite database

Once you have sqlite3 started, you can create a new database using the following command:

sqlite> .open newDatabase.db

Now, you have a new SQLite database named newDatabase.db.

To know more about creating a database you can read our article SQLite-How to create a database?

Creating a new table with constraints

To create a new table with constraints, you use the CREATE TABLE SQL command followed by the table name and the columns with their datatypes and constraints.

To know more about creating a table you can read our article SQLite-How to create a Table?

General Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Here’s an example:

sqlite> CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name TEXT NOT NULL,
    Age INT CHECK(Age >= 18),
    Email TEXT UNIQUE,
    City TEXT DEFAULT 'Unknown'
);

In this example, the Employees table is created with the following constraints:

The ID field is the primary key. It must contain a unique value and cannot be NULL.
The Name field cannot be NULL.
The Age field must be greater than or equal to 18.
The Email field must contain a unique value.
The City field will default to ‘Unknown’ if no value is specified.

Examples

NOT NULL

CREATE TABLE Employees (
    ID INT NOT NULL,
    Name TEXT NOT NULL
);

This query is used to create a table named “Employees” in a database. The table has two columns: “ID” and “Name”.

The “ID” column is of type INT (integer) and is marked as NOT NULL, which means it cannot contain a null value (empty or missing value).
The “Name” column is of type TEXT, which can store alphanumeric characters, and it is also marked as NOT NULL.

DEFAULT

CREATE TABLE Employees (
    ID INT NOT NULL,
    Name TEXT NOT NULL,
    City TEXT DEFAULT 'Unknown'
);

This query creates a table called “Employees” with three columns, where “ID” and “Name” are required to have non-null values, and “City” has a default value of ‘Unknown’ if not explicitly provided.

UNIQUE

CREATE TABLE Employees (
    ID INT NOT NULL,
    Email TEXT UNIQUE
);

This query creates a table named “Employees” with two columns, where “ID” is required to have non-null values, and “Email” must have a unique value for each row.

PRIMARY KEY

CREATE TABLE

 Employees (
    ID INT PRIMARY KEY,
    Name TEXT NOT NULL
);

This query creates a table called “Employees” with two columns, where “ID” serves as the primary key, uniquely identifying each row, and “Name” is required to have non-null values.

CHECK

CREATE TABLE Employees (
    ID INT NOT NULL,
    Age INT CHECK(Age>=18)
);

This query creates a table called “Employees” with two columns, where “ID” is required to have non-null values, and “Age” must have a value greater than or equal to 18 due to the CHECK constraint.

DbSchema Designer alt >

Implement Constraints using DbSchema

DbSchema is a SQLite client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
Constraints can be managed in DbSchema by simply double-clicking any table header. In the table dialog you can create new foreign keys or check constraints.

Open DbSchema and connect to your SQLite database.
Right-click on the table where you want to add constraints, and select ‘Open in Diagram’.
In the table layout, select the column where you want to add constraints.
On the right panel, you can set the constraint from the ‘Constraint’ dropdown.
For a CHECK constraint, select ‘Check Constraint’ from the dropdown and enter the condition in the ‘Expression’ box.
Click ‘Apply’ to save changes.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Understanding and implementing constraints is crucial in maintaining the accuracy and consistency of the data in a SQLite database. Whether you’re using sqlite3 command-line shell or DbSchema, applying constraints effectively ensures data integrity and facilitates efficient data management.