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

--

--

--

Engineering and technology articles for developers, written and curated by Ivyers. The views expressed are those of the authors and don’t necessarily reflect those of IvyMobility.

Recommended from Medium

Use Local Storages instead of Databases.

MDF file size stays the same after DELETE records

Building a Security Tool for Developers with Alok Shukla

Clean Architecture in Go

Why Your Should Be Worried By The Cloud And Start Your Journey Right Now

Python vs. Ruby vs. Node.js — Which platform is a fit for your project?

Proton Technical How To #3

The Web API Authentication guide, Basic Auth

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tony Wilson jesuraj

Tony Wilson jesuraj

Insane || iOS developer

More from Medium

Upload Mobile app to BrowserStack with GitHub Actions

Upload mobile app to BrowserStack with GitHub Actions

How to setup Appium Android & iOS

Making a Simple To-Do List Application with HMS Kits

How to register keyboard shortcuts in Android’s menu to inform users