![]() |
Recipe #5: |
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:
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); |
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); |
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:
One unique exception exists: columns declared as INTEGER PRIMARY KEY absolutely require integer values. |
ALTER TABLE peoples2 ADD COLUMN cell_phone TEXT; |
Yet another SQLite's own very peculiar design choice.
|
ALTER TABLE peoples2 RENAME TO peoples_ok; |
DROP TABLE peoples; |
CREATE INDEX idx_peoples_phone ON peoples_ok (phone); |
DROP INDEX idx_peoples_phone; |
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 |
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 |
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 |
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. |