DbSchema | How to Implement Constraints in Sqlite?

Publish on DbSchema Blog >>>

Table of Contents

1. Introduction
2. Prerequisites
3. What is a Constraint?
4. Advantages and Limitations of Constraints
5. SQLite Constraints
6. Implementing Constraints in sqlite3
7. Implementing Constraints in DbSchema
8. Conclusion
9. 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

1. Data Accuracy: Constraints ensure that the data adheres to the defined rules, maintaining __data accuracy`.

2. Data Consistency: By enforcing rules, constraints ensure the __consistency` of data across the database.

3. Preventing Invalid Data Entry: Constraints prevent __invalid data` from being entered into the database.

Limitations

1. Performance Impact: Constraints can impact database __performance`, especially when working with large data sets, as each data modification requires constraint checks.

2. Increased Complexity: They can increase the __complexity` of SQL queries and database design.

3. 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

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.

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.

1. __Open DbSchema` and connect to your SQLite database.
2. Right-click on the table where you want to add __constraints`, and select ‘Open in Layout’.
3. In the __table layout`, select the column where you want to add constraints.
4. On the right panel, you can set the constraint from the ‘Constraint’ dropdown.
5. For a __CHECK` constraint, select ‘Check Constraint’ from the dropdown and enter the condition in the ‘Expression’ box.
6. 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.

References

1. SQLite Documentation
2. DbSchema Documentation
3. SQL Constraints - W3Schools