Skip to content

1 4 7 2 complements

Schémas relationnel ⅔ : compléments

Supports complémentaires

Pas de vidéo pour cette session qui présente quelques commandes utilitaires.

La clause check

La clause check exprime des contraintes portant soit sur un attribut, soit sur un nuplet. La condition elle-même peut être toute expression suivant la clause where dans une requête SQL. Les contraintes les plus courantes sont celles consistant à restreindre un attribut à un ensemble de valeurs, comme expliqué ci-dessous. On peut trouver des contraintes arbitrairement complexes, faisant référence à d'autres tables.

Voici un exemple simple qui restreint les valeurs possibles des attributs année et genre dans la table Film.

create table Film  (idFilm integer not null, 
                   titre    varchar (50) not null,
                   année    integer 
                      check (année between 1890 and 2020) not null,
                   idRéalisateur    integer,
                   genre varchar (20) l
                         check (genre in ('Histoire','Western','Drame')) not null,
                   résumé      varchar(255),
                   codePays    varchar (4),
                   primary key (idFilm),
                   foreign key (idRéalisateur) references Artiste,
                   foreign key (codePays) references Pays)

Au moment d'une insertion dans la table Film, ou d'une modification de l'attribut année ou genre, le SGBD vérifie que la valeur insérée dans genre appartient à l'ensemble énuméré défini par la clause check.

Une autre manière de définir, dans la base, l'ensemble des valeurs autorisées pour un attribut -- en d'autres termes, une codification imposée -- consiste à placer ces valeurs dans une table et la lier à l'attribut par une contrainte de clé étrangère. C'est ce que nous pouvons faire par exemple pour la table Pays.

create table Pays (code    varchar(4) not null,
                   nom  varchar (30) default 'Inconnu' not null,
                  langue varchar (30) not null,
                  primary key (code));
 insert into Pays (code, nom, langue) values ('FR', 'France', 'Français');
 insert into Pays (code, nom, langue) values ('USA', 'Etats Unis', 'Anglais');
 insert into Pays (code, nom, langue) values ('IT', 'Italie', 'Italien');
 insert into Pays (code, nom, langue) values ('GB', 'Royaume-Uni', 'Anglais');
 insert into Pays (code, nom, langue) values ('DE', 'Allemagne', 'Allemand');
 insert into Pays (code, nom, langue) values ('JP', 'Japon', 'Japonais');

Si on ne fait pas de vérification automatique, soit avec check, soit avec la commande foreign key, il faut faire cette vérification dans l'application, ce qui est plus lourd à gérer.

Modification du schéma

La création d'un schéma n'est qu'une première étape dans la vie d'une base de données. On est toujours amené par la suite à créer de nouvelles tables, à ajouter des attributs ou à en modifier la définition. La forme générale de la commande permettant de modifier une table est :

alter table <nomTable> <action> <description> 

action peut être principalement add, modify, drop ou rename et description est la commande de modification associée à action. La modification d'une table peut poser des problèmes si elle est incompatible avec le contenu existant. Par exemple passer un attribut à not null implique que cet attribut a déjà des valeurs pour tous les nuplets de la table.

Modification des attributs

Voici quelques exemples d'ajout et de modification d'attributs. On peut ajouter un attribut region à la table Internaute avec la commande :

alter table Internaute add région varchar(10)

S'il existe déjà des données dans la table, la valeur sera à null ou à la valeur par défaut. La taille de région étant certainement insuffisante, on peut l'agrandir avec modify, et la déclarer not null par la même occasion :

alter table Internaute modify région varchar(30) not null

Il est également possible de diminuer la taille d'un attribut, avec le risque d'une perte d'information pour les données existantes. On peut même changer son type, pour passer par exemple de varchar à integer, avec un résultat imprévisible.

La commande alter table permet d'ajouter une valeur par défaut.

alter table Internaute add région set default 'Corse'

Enfin, on peut détruire un attribut avec drop.

alter table Internaute drop région

De plus, chaque système propose des commandes non normalisées. MySQL par exemple dispose d'une commande truncate pour "vider" une table rapidement, sans effectuer de contrôle (!) À vous d'éplucher la documentation pour ces aspects spécifiques.

Création d'index

Pour compléter le schéma d'une table, on peut définir des index. Un index offre un chemin d'accès aux nuplets d'une table qui est considérablement plus rapide que le balayage de cette table -- du moins quand le nombre de nuplets est très élevé. Les SGBD créent systématiquement un index sur la clé primaire de chaque table. Il y a plusieurs raisons à cela ;

  • l'index permet de vérifier rapidement, au moment d'une insertion, que la clé n'existe pas déjà ;
  • l'index permet également de vérifier rapidement la contrainte d'intégrité référentielle : la valeur d'une clé étrangère doit toujours être la valeur d'une clé primaire.
  • beaucoup de requêtes SQL, notamment celles qui impliquent plusieurs tables (jointure), se basent sur les clés des tables pour reconstruire les liens. L'index peut alors être utilisé pour améliorer les temps de réponse.

Un index est également créé pour chaque clause unique utilisée dans la création de la table. On peut de plus créer d'autres index, sur un ou plusieurs attributs, si l'application utilise des critères de recherche autres que les clés primaire ou secondaires.

La commande pour créer un index est la suivante :

create [unique] index <nomIndex>  on <nomTable> (<attribut1> [, ...])

L'option unique indique qu'on ne peut pas trouver deux fois la même clé dans l'index. La commande ci-dessous crée un index de nom idxNom sur les attributs nom et prénom de la table Artiste. Cet index a donc une fonction équivalente à la clause unique déjà utilisée dans la création de la table.

create unique index idxNom  on Artiste (nom, prénom)

On peut créer un index, cette fois non unique, sur l'attribut genre de la table Film.

create index idxGenre on Film (genre)

Cet index permettra d'exécuter très rapidement des requêtes SQL ayant comme critère de recherche le genre d'un film.

select * from Film where genre = 'Western'

Cela dit il ne faut pas créer des index à tort et à travers, car ils ont un impact négatif sur les commandes d'insertion et de destruction. À chaque fois, il faut en effet mettre à jour tous les index portant sur la table, ce qui représente un coût certain.

Pour en savoir plus sur les index, et en général sur la gestion de l'organisation des données, je vous renvoie à la seconde partie du cours disponible à http://sys.bdpedia.fr.