SpatiaLite Cookbook

	Recette #5: Creer une table
Février 2011

Table des matières

Vous êtes maintenant conscient qu'en SQL, la performance et l’efficience dépend essentiellement de la structure de la DBB:

créer des tables (et colonnes) de la façon la plus appropriée.
identifier des relations liant différentes tables.
créer des index pour les relations fréquemment utilisées.
identifier des contraintes utiles, afin de préserver l'intégrité des données autant que possible.

Il est maintenant temps d'examiner ce point en détail.

note: dans le jargon SGBD/SQL ceci s'appelle DDL [Data Definition Language], et est a opposé au DML [Data Manipulation Language], i.e. SELECT, INSERT etc ...

CREATE TABLE peoples (
  first_name TEXT,
  last_name TEXT,
  age INTEGER,
  gender TEXT,
  phone TEXT);

Cette requête va créer une table très simple nommée peoples:

la définition de chaque colonne doit spécifier au minimum le type de donnée, comme TEXT ou INTEGER (entier)
note: les types de données supportés par SQLite diffèrent des autres SGBD:
nous verrons cela plus en détail.

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

Voici une version améliorée de la même table:

on a rajouté une colonne id, déclarée comme PRIMARY KEY AUTOINCREMENT
- insérer une PRIMARY KEY a chaque table est toujours une bonne idée.
- la clause AUTOINCREMENT stipule à SQLite de générer automatiquement une valeur unique pour chaque ligne.
on a ajouté une clause NOT NULL pour les colonnes first_name et last_name.
- ceci définit une première sorte de contrainte:les valeurs NULL seront rejetées de cette colonne
- en d'autres termes, first_name et last_name devront obligatoirement contenir une valeur explicite.
on a ajouté CONSTRAINT ... CHECK (...) pour les colonnes age et gender (genre).
- ceci définit un deuxième type de contrainte: les valeurs ne satisfaisant pas le critère CHECK (...) seront rejetés.
- la colonne age acceptera uniquement un age adulte raisonnable.
- la colonne gender acceptera uniquement les valeurs 'M' or 'F'.
- note: nous n'avons pas déclaré de clause NOT NULL, ainsi age = NULL et gender = NULL seront considérés comme des valeurs valides.

à propos des types de données supportés par SQLite

Très simplement SQLite ne gère pas de type de donnée ...
Vous êtes absolument libre d'insérer n'importe quel type de donnée dans chaque colonne: le type de donnée déclaré pour une colonne ne constitue pas une réelle contrainte.
Ceci n'est pas un bug: c'est un choix de développement de SQLite.
Attention: ce n'est pas le cas des autres SGBD, et vous risquez d'avoir des surprises lors d'export vers ces SGBD.

SQLite supporte les types de donnée suivants:

NULL: pas de valeur.
INTEGER: entiers jusqu'à 64bit
DOUBLE: flottant, chiffres a virgule.
TEXT: text encodé en UTF-8
BLOB: Binary Long Object, utilisé pour les colonnes géométriques

Rappel: chaque cellule (intersection ligne/colonne) peut stocker un type de donnée arbitraire.
Une seule exception: les colonnes déclarées INTEGER PRIMARY KEY nécessitent un entier.

ALTER TABLE peoples2
ADD COLUMN cell_phone TEXT;

Vous pouvez ajouter de nouvelles colonnes, même après la création de la table.

Ici encore, un choix propre à SQLite:

la suppression de colonne n'est pas autorisé.
renommer les colonnes n'est pas autorisé.

i.e. une fois la colonne créée, vous ne pouvez plus modifier sa configuration.

ALTER TABLE peoples2
RENAME TO peoples_ok;

Cependant, vous pouvez simplement copier une table.

DROP TABLE peoples;

cette requête supprimera définitivement la table peoples.

CREATE INDEX idx_peoples_phone
ON peoples_ok (phone);

création d'un INDEX

DROP INDEX idx_peoples_phone;

suppression d'un INDEX

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

un index peut supporter plusieurs colonnes.
la clause UNIQUE rajoute une contrainte:
une fois qu'une valeur est insérée, l'insertion de la même valeur échouera.

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

Vous pouvez utiliser PRAGMA table_info(...) afin de lister les colonnes d'une table.

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

utiliser PRAGMA index_list(...) et PRAGMA index_info(...) permet de lister les informations sur les index.

Table des matières

	Author: Alessandro Furieri a.furieri@lqt.it Traduced from English by RIVIERE Romain
	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.