SQLite constraints

SQLite constraint

NOT NULL constraints

By default, columns can hold NULL values. If you do not want a column to have an NULL value, you need to define this constraint on that column, specifying that NULL values are not allowed on that column.

NULL is not the same as no data, it represents unknown data.

Instance

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

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

DEFAULT constraints

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

Instance

For example, the following SQLite statement creates a new table COMPANY and adds five columns. H ere, the SALARY column is set to 5000.00 by default. So when the INSERT INTO statement does not provide a value for the column, the column is 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 5000.00
);

UNIQUE constraints

UNIQUE constraints prevent two records from having the same value in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having the same age.

Instance

For example, the following SQLite statement creates a new table COMPANY and adds five columns. Here, the AGE column is set to UNIQUE, so you cannot have two records of 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 5000.00
);

PRIMARY KEY constraints

The PRIMARY KEY constraint uniquely identifies each record in the database table. Y ou can have more than one UNIQUE column in a table, but you can only have one primary key. P rimary keys are important when designing database tables. The primary key is the only ID.

We use the primary key to refer to the rows in the table. Y ou can create relationships between tables by setting the primary key to the foreign key of another table. Because of "long-standing coding supervision," in SQLite, the primary key can be NULL, which is different from other databases.

The primary key is a field in the table that uniquely identifies the rows/records in the database table. T he primary key must contain a unique value. The primary key column cannot have an NULL value.

A table can have only one primary key, which can consist of one or more fields. When multiple fields act as primary keys, they are called composite keys.

If a table defines a primary key on any field, two records cannot have the same value on those fields.

Instance

You've seen various instances of comAPNY tables that we've created 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 constraints

The CHECK constraint enables the entry of a condition for a record to check the value. If the condition value is false, the record violates the constraint and cannot be entered into the table.

Instance

For example, the following SQLite creates a new table COMPANY and adds five columns. Here, we add CHECK for the SALARY column, so 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)
);

Remove the constraint

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