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 defining NOT NULL constraint the value of a column can’t be NULL

Example

CREATE TABLE superHeros(
   ID             INT NOT NULL,
   NAME           TEXT,   
   AGE            INT  NOT NULL,
);

Now you need to enter data in IDand 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 using DEFAULT CONSTRAINT.
• If the column name was not specified while inserting then it will not an null , it will the DEFAULT 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

Don’t hesitate to ask any doubts, just reach me at twitter