SQLite Constraints

Constraints are rules enforced on the data columns of the table. These are used to limit the data types that can enter the 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 to a single column, while table-level constraints apply to the entire table.

The following are commonly used constraints available in SQLite.

• NOT NULL Constraint - ensures that the column cannot be NULL values.

• DEFAULT Constraint - provides a default value for the column if not specified.

• UNIQUE Constraint - ensures that all values in the column are different.

• PRIMARY Key − Uniquely identifies each row in the database table/Record.

• CHECK Constraint - ensures that all values in the column meet certain conditions.

NOT NULL constraint

By default, a column can save NULL values. If you do not want a column to have NULL values, you need to define such a constraint on this column to specify that the column does not allow NULL now.

NULL is equivalent to no data, representing unknown data.

Example

For example, the following SQLite statement creates a new table named COMPANY and adds five columns, of which three columns (ID, NAME, and AGE) specify that NULL is 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 constraint

When the INSERT INTO statement does not provide a specific value, the DEFAULT constraint will provide a default value for the column.

Example

For example, the following SQLite statement creates a new table named COMPANY and adds five columns. Here, the SALARY column is set to default by default.5000.00, so if the INSERT INTO statement does not provide a value for this column, the column will be set to the default value by default.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 specific column. For example, in the COMPANY table, you may want to prevent two or more people from having the same age.

Example

For example, the following SQLite statement creates a new table named COMPANY and adds five columns. Here, the AGE column is set to UNIQUE, so you cannot have 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 the database table. There can be more UNIQUE columns, but there is only one primary key in the table. It is important to design the database table, as the primary key is a unique ID.

We use them to refer to table rows. When creating relationships between tables, the primary key becomes a foreign key in other tables. Due to 'long-term coding supervision', the primary key in SQLite can be NULL. This is not the case in other databases.

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

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

If a table defines a primary key field(s) on any table, then two records cannot have the same value field(s).

Example

You have seen many examples above, in which we created a 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

CHECK constraints allow conditions to check the values entered into the records. If the calculation of the condition results in false, the record violates the constraint and is not entered into the table.

Example

For example, the following SQLite creates a new table named COMPANY and adds five columns. Here, we added a CHECK column with SALARY, so you cannot have any SALARY 0.

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)
);

Drop Constraint

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows users to rename a table or add new columns to an existing table. Columns cannot be renamed, deleted, or constraints added or removed from the table.