SpatiaLite logo

Recipe #5:
Creating a new table (and related paraphernalia)

2011 January 28

Previous Slide Table of Contents Next Slide

You are now well conscious that SQL overall performance and efficiency strongly depend on the underlying database layout, i.e. the following design choices are critical:
  • defining tables (and columns) in the most appropriate way.
  • identifying relations connection different tables.
  • supporting often-used relations with an appropriate index.
  • identifying useful constraints, so to preserve data consistency and correctness as much as possible.
It's now time to examine in deeper detail such topics.

Pedantic note: in DBMS/SQL own jargon all this is collectively defined as DDL [Data Definition Language], and is intended as opposed to DML [Data Manipulation Language], i.e. SELECT, INSERT and so on.

CREATE TABLE peoples (
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  gender TEXT,
  phone TEXT);
This statement will create a very simple table named peoples:

CREATE TABLE peoples2 (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
    CONSTRAINT age_verify
      CHECK (age BETWEEN 18 AND 90),
  gender TEXT
    CONSTRAINT gender_verify
      CHECK (gender IN ('M', 'F')),
  phone TEXT);
This one is more sophisticated version of the same table:

about SQLite data-types

Very shortly said: SQLite hasn't data-types at all ...
You are absolutely free to insert any data-type on every column: the column declared data-type simply have a decorative role, but isn't neither checked not enforced at all.
This one absolutely is not a bug: it's more a peculiar design choice.
Anyway, any other different DBMS applies strong data-type qualification and enforcement, so the SQLite's own behavior may easily look odd and puzzling. Be warned.

Anyway SQLite internally supports the following data-types:
  • NULL: no value at all.
  • INTEGER: actually 64bit integers, so to support really huge values.
  • DOUBLE: floating point, double precision.
  • TEXT: any UTF-8 encoded text string, of unconstrained arbitrary length.
  • BLOB: any generic Binary Long Object, of unconstrained arbitrary length.
Remember: each single cell (row/column intersection) can store any arbitrary data-type.
One unique exception exists: columns declared as INTEGER PRIMARY KEY absolutely require integer values.

ALTER TABLE peoples2
  ADD COLUMN cell_phone TEXT;
You can add any further column even after the initial table creation.

Yet another SQLite's own very peculiar design choice.
  • dropping columns is unsupported.
  • renaming columns is unsupported.
i.e. once you've created a column there is no way at all to change its initial definition.

ALTER TABLE peoples2
  RENAME TO peoples_ok;
Anyway you are absolutely free to change the table name.

DROP TABLE peoples;
And this will completely erase the table (and its whole content) from the DB.

CREATE INDEX idx_peoples_phone
  ON peoples_ok (phone);
This will create an Index.

DROP INDEX idx_peoples_phone;
And this will destroy the same Index.

CREATE UNIQUE INDEX idx_peoples_name
  ON peoples_ok (last_name, first_name);

PRAGMA table_info(peoples_ok);

cid name type notnull dflt_value pk
1 first_name TEXT 1 NULL 0
2 last_name TEXT 1 NULL 0
3 age INTEGER 0 NULL 0
4 gender TEXT 0 NULL 0
5 phone TEXT 0 NULL 0
6 cell_phone TEXT 0 NULL 0
You can use PRAGMA table_info(...) in order to query a table layout.

PRAGMA index_list(peoples_ok);

seq name unique
0 idx_peoples_phone 0
1 idx_peoples_name 1

PRAGMA index_info(idx_peoples_name);

seqno cid name
0 2 last_name
1 1 first_name
And using PRAGMA index_list(...) and PRAGMA index_info(...) you can easily query the corresponding Index layout.
Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.