Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Operators Sqlite Drop Table ❯

SQLite Constraints

Constraints are rules enforced on the data columns of a table. These are used to limit the type of data that can be inserted into a table. This ensures the accuracy and reliability of the data in the database.

Constraints can be column-level or table-level. Column-level constraints apply only to one column, while table-level constraints apply to the entire table.

The following are commonly used constraints in SQLite.

-

NOT NULL Constraint: Ensures that a column cannot have a NULL value.

-

DEFAULT Constraint: Provides a default value for a column when no value is specified.

-

UNIQUE Constraint: Ensures that all values in a column are different.

-

PRIMARY KEY Constraint: Uniquely identifies each row/record in a database table.

-

CHECK Constraint: Ensures that all values in a column satisfy certain conditions.

NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a column to have a NULL value, you need to define this constraint on the column, specifying that the column does not allow NULL values.

NULL is different from no data; it represents unknown data.

Example

For example, the following SQLite statement creates a new table COMPANY and adds five columns, where the ID, NAME, and AGE columns are specified not to accept NULL values:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when an INSERT INTO statement does not provide a specific value.

Example

For example, the following SQLite statement creates a new table COMPANY and adds five columns. Here, the SALARY column is set to default to 5000.00. So when an INSERT INTO statement does not provide a value for this column, it will be set to 5000.00:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

UNIQUE Constraint

The UNIQUE constraint prevents two records from having identical values in a particular column. For example, in the COMPANY table, you might want to prevent two or more people from having the same age.

Example

For example, the following SQLite statement creates a new table COMPANY and adds five columns. Here, the AGE column is set to UNIQUE, so no two records can have the same age:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. A table can have multiple UNIQUE columns but only one primary key. Primary keys are important when designing database tables. The primary key is a unique ID.

We use the primary key to refer to rows in a table. Relationships between tables can be created by setting the primary key as a foreign key in other tables. Due to "long-standing coding oversight," in SQLite, the primary key can be NULL, which is different from other databases.

The primary key is a field in a table that uniquely identifies each row/record in a database table. The primary key must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which can consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

If a primary key is defined on any field(s) of a table, no two records can have the same value in those fields.

Example

We have already seen various examples of creating the COMPANY table with ID as the primary key:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy certain conditions. CHECK constraint enables input of a record to check the value condition. If the condition value is false, the record violates the constraint and cannot be input into the table.

Example

For instance, the following SQLite creates a new table COMPANY and adds five columns. Here, we add a CHECK to the SALARY column so that the salary cannot be zero:

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Removing Constraints

SQLite supports a limited subset of ALTER TABLE. In SQLite, the ALTER TABLE command allows users to rename a table or add a new column to an existing table. Renaming a column, dropping a column, or adding or removing constraints from a table is not possible.

❮ Sqlite Operators Sqlite Drop Table ❯