SQL statements to optimize the use of a database

Introduction

In this lesson, we will cover a few additional tools and statements to optimize the use of an SQLite database.

By using various CONSTRAINTS when creating a new table, the database software can perform certain checks. These checks no longer need to be programmed by the developer.

The CREATE INDEX statement can be used to optimize the performance of certain queries. In contrast, the CREATE VIEW statement allows developers to simplify certain queries and makes it possible to hide specific columns in a table.

Constraints

In the lesson Create an SQLite table we already used the PRIMARY KEY clause to select a column with a unique value, which allows us to refer to a unique row in a table. Primary key values will be used to create relations between tables. There can only be one primary key in a table.

CREATE TABLE Student(
   StudentID INT PRIMARY KEY,
   StudentName TEXT,
   StudentAddress TEXT,
   StudentBankAccount TEXT,
   StudentAge INT,
   StudentNationality Text
);

The UNIQUE constraint prevents the same value from being used in another row (for example a StudentBankAccount cannot be used for a different student):

CREATE TABLE Student(
   StudentID INT PRIMARY KEY,
   StudentName TEXT,
   StudentAddress TEXT,
   StudentBankAccount TEXT UNIQUE ,
   StudentAge INT,
   StudentNationality Text
);

The NOT NULL constraint for a specific field requires that a value be specified for that field when using an INSERT statement (for example an INSERT statement without specifying a StudentName is not allowed):

CREATE TABLE Student(
   StudentID INT PRIMARY KEY,
   StudentName TEXT NOT NULL,
   StudentAddress TEXT,
   StudentBankAccount TEXT UNIQUE,
   StudentAge INT,
   StudentNationality Text
);

The CHECK constraint defines a condition that must be checked when inserting a value in a column (for example: the value in the column StudentAge must be greater than 15):

CREATE TABLE Student(
   StudentID INT PRIMARY KEY,
   StudentName TEXT NOT NULL,
   StudentAddress TEXT,
   StudentBankAccount TEXT UNIQUE,
   StudentAge INT CHECK ("StudentAge" > 15),
   StudentNationality Text
);

The DEFAULT constraint defines a default value for a column (for example, the StudentNationality column is set to “Belgian” if no value is specified in the INSERT statement):

CREATE TABLE Student(
   StudentID INT PRIMARY KEY,
   StudentName TEXT NOT NULL,
   StudentAddress TEXT,
   StudentBankAccount TEXT UNIQUE,
   StudentAge INT CHECK ("StudentAge" > 15),
   StudentNationality Text DEFAULT Belgian
);

Indexes

Indexes are special lookup tables (also known as index tables) created to speed up queries. The CREATE INDEX statement is used to create an index:

CREATE INDEX index_name
ON table_name(column_name_list);

In the following example, an index (StudentName_Index) is created based on a student’s name:

CREATE INDEX StudentName_index
ON Student(StudentName);

The index above can significantly speed up searches by student name.

There is no limit to the number of indexes in a table. You can also create an index on multiple columns, separating the column names with commas.

Views

A view is a selection of rows and/or columns from one or more tables. Views are very useful to restrict access to specific data in the database.

The CREATE VIEW statement is used to define views. The general syntax is as follows:

CREATE VIEW view_name AS
SELECT column_name_list
FROM table_name;

In the following example, a view is created in which the student’s age is no longer available in the view:

CREATE VIEW StudentNoAge As
SELECT StudentID, StudentName, StudentAddress, StudentBankAccount, StudentNationality
FROM Student;

A VIEW can be thought of as a virtual table. You use the view_name (StudentNoAge) to refer to this virtual table.

SELECT *
FROM StudentNoAge;