SQLite constraints are rules that are enforced by the database management system to maintain the integrity and consistency of data in a SQLite database. These constraints are used to ensure that data entered into tables meets certain criteria, such as uniqueness, referential integrity, and data type compatibility.
SQLite constraints are a great way to ensure the data in your database is valid and consistent. By using SQLite constraints, you can avoid having to write custom validation code. SQLite constraints are also easy to use and understand.
Syntax
CREATE TABLE table_name( column_name1 datatype constraint, column_name2 datatype constraint, column_name3 datatype constraint, ... );
SQLite supports the following types of constraints:
Primary Key
A PRIMARY KEY is a column or set of columns that uniquely identify a row in a table. SQLite automatically creates an index for each primary key column.
Foreign Key
A FOREIGN KEY is a column or set of columns that uniquely identify a row in another table. SQLite automatically creates an index for each foreign key column.
Not null
A NOT NULL constraint is a column level constraint that requires a column to contain a non-NULL value. SQLite does not allow null values in a column with a not null constraint.
Check
A CHECK constraint is a column level constraint that requires a column to meet a certain condition. SQLite does not allow invalid data in a column with a check constraint.
Unique
A UNIQUE constraint is a column level constraint that requires all values in a column to be unique. SQLite does not allow duplicate values in a column with a unique constraint.
Autoincrement
An AUTOINCREMENT column is a column with a special property that automatically generates a sequence of integers. SQLite automatically creates an index for each autoincrement column.
In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database.
By using constraints, SQLite ensures that data in the database is consistent and reliable, reducing the risk of errors and data corruption.
Example
CREATE TABLE customer_type(
type_id INTEGER PRIMARY KEY AUTOINCREMENT,
type_name VARCHAR(100) NOT NULL,
comments VARCHAR(1000)
);
INSERT INTO customer_type (type_name, comments)
VALUES ('Individual', 'test 1');
INSERT INTO customer_type (type_name, comments)
VALUES ('Company', 'test 2');
CREATE TABLE customers(
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_name VARCHAR(200) NOT NULL,
customer_tax_no TEXT NOT NULL UNIQUE,
customer_type_id INTEGER NOT NULL,
CONSTRAINT fk_customer_type
FOREIGN KEY (customer_type_id)
REFERENCES customer_type (type_id)
);
INSERT INTO customers (customer_name, customer_tax_no, customer_type_id)
VALUES ('Tom', '111AAA', 1);
INSERT INTO customers (customer_name, customer_tax_no, customer_type_id)
VALUES ('Olivia', '222BBB', 1);
INSERT INTO customers (customer_name, customer_tax_no, customer_type_id)
VALUES ('Emma', '333CCC', 1);
INSERT INTO customers (customer_name, customer_tax_no, customer_type_id)
VALUES ('SQLite LTD', '444DDD', 2);
In the example above, two tables were created that contain constraints in the definition of the columns, but also a constraint at the table level. The first table created is customer_type, and the second is customers. In the CREATE TABLE statement of the customers table, all types of SQLite constraints were used and the connection was made with the customer_type table through the fk_customer_type foreign constraint.