Mastering in SQLite
Constraints in DB
Before getting into SQLite Functions, if you haven’t covered the previous topic- Data definition, then get into it.
To visit the introduction page to see the available topics click here.
A number of superheroes are available in our DB, click here to download it.
Constraints
Constraints are the rules enforced on data columns on the table. These are used to limit the type of data that can go into a table. In other words, controlling the data entry into the column.
NOT NULL Constraint
- By default, a column can have
null
values (if column not specified while inserting)but by definingNOT NULL
constraint the value of a column can’t beNULL
Example
CREATE TABLE superHeros(
ID INT NOT NULL,
NAME TEXT,
AGE INT NOT NULL,
);
Now you need to enter data in ID
and AGE
while inserting, you constrained that columns not to be null
DEFAULT constraint
- By default, a column can have
null
values (if column not specified while inserting the row ) but you can define any default value to that column usingDEFAULT CONSTRAINT.
- If the column name was not specified while inserting then it will not an
null
, it will theDEFAULT CONSTRAINT
you defined
Example
CREATE TABLE superHeros(
ID INT NOT NULL,
NAME TEXT,
AGE INT NOT NULL,
SALARY REAL DEFAULT 50000.00
);
It will 50000.00
if not specified while inserting the row
UNIQUE Constraint
- prevents two records from having identical values in a particular column
Example
CREATE TABLE superHeros(
ID INT NOT NULL,
NAME TEXT,
AGE INT NOT NULL UNIQUE,
SALARY REAL DEFAULT 50000.00
);
so in the age column, you cannot have two records with the same age
PRIMARY Key
- The primary key and unique constraints are the same. You can have many unique constraints column in a table but only one primary key column in a table
- If a table has a primary key defined on any field, then you cannot have two records having the same value of that field.
- And primary key cant be
NULL
Example
CREATE TABLE superHeros(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT NOT NULL UNIQUE,
SALARY REAL DEFAULT 50000.00
);
CHECK Constraint
check constraint is a condition to check the value being entered into a table column (specified). if the condition fails it will not enter into the table
Example
CREATE TABLE superHeros(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT CHECK(AGE < 18),
SALARY REAL DEFAULT 50000.00
);
if enter below 18 in the age column conditions fails and it will not enter into the table
AUTOINCREMENT
- Autoincrement is used for the auto increasing value of the column in the table.
- Autoincrement supports only on INTEGER field
CREATE TABLE table_name(
column1 INTEGER AUTOINCREMENT,
column2 datatype,
)
it will increase the value one by one
Dropping Constraint
It is not possible to remove constraints from a table.
Reach out the next topic- Changing data
(Note: just ignore it)
They can put Constraints for the table but not for the developers