SQLite Constraints

NOT NULL Constraint

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

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

Example

For example, the following SQLite statement creates a new table COMPANY with five columns, where the three columns ID, NAME, and AGE 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 the INSERT INTO statement does not provide a specific value.

Example

For example, the following SQLite statement creates a new table COMPANY with five columns. Here, the SALARY column is set to default to 5000.00. So when the 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 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.

Example

For example, the following SQLite statement creates a new table COMPANY with five columns. Here, the AGE column is set to UNIQUE, so there cannot be two records with 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. There can be multiple UNIQUE columns in a table, but only one primary key. Primary keys are important when designing database tables. A primary key is a unique ID.

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

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

A table can have only one primary key, which can consist of one or more fields. When multiple fields are used as the primary key, they are calleda composite key.。

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

Example

We have already seen various examples of creating the COMAPNY 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 enables a condition to check the values when entering a record. If the condition evaluates to false, the record violates the constraint and cannot be entered into the table.

Example

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

Dropping Constraints

In SQLite, to drop constraints from a table, you typically need to use theALTER TABLEstatement and specify the type of constraint to be dropped.

Dropping the primary key constraint:

ALTER TABLE table_name
DROP CONSTRAINT primary_key_name;

Here,table_nameis the name of the table you are operating on,primary_key_nameis the name of the primary key constraint to be dropped.

Dropping the unique constraint:

ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;

Similarly,table_nameis the table name,unique_constraint_nameis the name of the unique constraint to be dropped.

Dropping the foreign key constraint:

ALTER TABLE table_name
DROP CONSTRAINT foreign_key_constraint_name;

Here,table_nameis the table name,foreign_key_constraint_nameis the name of the foreign key constraint to be dropped.

Other Extensions