SpatiaLite Cookbook

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

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:

each individual column definition must at least specify the corresponding data-type, such as TEXT or INTEGER
please note: data-type handling in SQLite strongly differs from others DMBS implementations:
but we'll see this in more detail later.

CREATE TABLE peoples2 (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  age INTEGER
    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:

we have added an id column, declared as PRIMARY KEY AUTOINCREMENT
- inserting a PRIMARY KEY on each table always is a very good idea.
- declaring an AUTOINCREMENT clause we'll request SQLite to automatically generate unique values for this key.
we have added a NOT NULL clause for first_name and last_name columns.
- this will declare a first kind of constraint: NULL values will be rejected for these columns.
- in other words, first_name and last_name absolutely have to contain some explicitly set value.
we have added a CONSTRAINT ... CHECK (...) for age and gender columns.
- this will declare a second kind of constraint: values failing to satisfy the CHECK (...) criterion will be rejected.
- the age column will now accept only reasonable adult ages.
- and the gender column will now accept only 'M' or 'F' values.
- please note: we have not declared the NOT NULL clause, so age = NULL and gender = NULL are still considered to be valid values.

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);

an Index can support more columns.
declaring the UNIQUE clause implements a further constraint:
once some value is already defined, any further attempt to insert the same value will then fail.

PRAGMA table_info(peoples_ok);

cid	name	type	notnull	dflt_value	pk
0	id	INTEGER	1	NULL	1
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

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	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.