SQL - créationDéfinition des tables

Une fois bien calés sur la création d'une base de données (ouais c'était il y a deux chapitres mais bon logiquement vous l'avez vu il y a peu de temps) et le tpage des données, il ne reste plus qu'à créer nos tables, et à comprendre ce que l'on peut faire avec pour leur structure, ainsi qu'à les remplir, parce que des tables vides et propres c'est bien, mais il faudrait qu'on mette des choses dedans pour que ça prenne tout son sens.

Création / suppression des tables

Il existe une seule commande de création des tables. C'est une fonction create table avec des paramètres, les attributs de la table et les contraintes.
Voici la syntaxe exacte: create table ma_table(attr1 type, attr2 type,...);

Pour la suppression des tables, il n'existe qu'une seule méthode, la fonction drop table dont voic la syntaxe: drop table ma_table;. On peut supprimer plusieurs tables en même temps en les séparant par des virgules.

Afin de rester dans la logique du cours, nous allons en exemple créer les tables de notre base de données R2
  1. create table personne(id int, nom varchar(20), adresse varchar(20));
  2. create table cours(cnum int, descr varchar(20));
  3. create table affectation(id int, cnum int, note int);

"Donc si je lance 10 fois la commande, ça me créera 10 fois ma table?"
Et bien non! Si vous tentez de créer une table qui existe déjà vous aurez une erreur. Pour éviter cette erreur ce n'est pas très compliqué. Pour la création il suffit de rajouter if not exists, et pour la suppression if exists. Ainsi, il tentera de créer la table si elle n'existe pas, et tentera de supprimer une table uniquement si elle existe.

  1. create table if not exists personne(id int, nom varchar(20), adresse varchar(20));
  2. drop table if exists affectation;
  3. drop table if exists personne,cours;

Modification de la structure des tables

Quand on crée une table, on indique les attributs, ainsi que le type de chacun d'entre eux. Mais il peut arriver que l'on se trompe dans la structure, ou qu'on veuille la faire évoluer. On a deux méthodes: La méthode bourrin, on supprime la table et on la recrée, et puis les données dedans tant pis! OU ALORS... on modifie la structure de la table. Ainsi les données resteront présentes, ce qui est tout de même nettement mieux.

Voilà la structure de la requête: alter table ma_table...

"Non mais c'est quoi encore ces ...? On peut pas faire une requête simple comme pour créer ou supprimer une table?"
On pourrait en effet faire une seule requête par modification. Mais on peut en faire beaucoup, donc le plus simple est de créer une seule requête alter table, avant de spécifier ce qu'on veut modifier derrière. Et là vous allez voir qu'on peut en changer des choses!
Voilà les différents cas que vous pourrez trouver:
rename to ma_table2
add ma_col typecolonne
change ma_col ma_col2 typecolonne
modify ma_col typecolonne
drop ma_col
add unique mon_index(ma_col) /
drop index mon_index
add primary key(ma_col)
drop primary key
add foreign key(ma_col) references ma_table2(ma_col2)
add constraint fk_ma_contrainte foreign key(ma_col) references ma_table2(ma_col2)
drop foreign key fk_ma_contrainte 

  1. alter table personne rename to personne2;
  2. alter table personne change adresse adresse2 varchar(20);
  3. alter table personne modify adresse varchar(20);
  4. alter table personne add adresse2 varchar(20);
  5. alter table personne drop adresse2;
  6. alter table personne add unique mon_adresse_unique(adresse);
  7. alter table personne drop index mon_adresse_unique;
  8. alter table personne add primary key(id);
  9. alter table personne drop primary key;
  10. alter table affectation add constraint fk_personne_id foreign key(id) references personne(id);
  11. alter table affectation drop foreign key fk_personne_id;

Vous voyez, on peut faire beaucoup de modifications une fois que nos tables sont créées. Rien n'est jamais gravé dans le marbre!


Gestion des clés

On l'a déjà vu précédemment, mais pour gérer les contraintes en SQL, il existe de nombreuses possibilités. Deux contraintes sont particulières et permettent de revenir sur les contraintes d'intégrité que l'on a vues dans un chapitre précédent:

Les clés primaires

Elles permettent de gérer principalement le concept d'unicité à l'intérieur d'une table.
Syntaxe de création: create table ma_table(id int primary key,...); ou create table ma_table(id int, ..., primarykey (id));

Il est également possible (et même recommandé dans de très nombreux cas maintenat, c'est même devenu un standard) de créer des clés auto-incrémentées. Ces attributs particuliers ne correspondent à aucun attribut mais servent uniquement à gérer l'unicité de chaque enregistrement.
La syntaxe est la suivante: create table ma_table(id int primary key auto_increment, ...); 

  1. create table personne(id int primary key, nom varchar(20), adresse varchar(20));
  2. create table cours(cnum int primary key, descr varchar(20));
  3. create table affectation(id int, cnum int, note int, primary key(id,cnum));

Les clés étrangères
Les clés étrangères permettent de restreindre les possibilités d'enregistrement en vérifiant la cohérence des données entre les tables. On cherche à éviter de rentrer une note d'un élève qui n'existe pas par exemple!

La syntaxe est la suivante:
Soit une table ma_table1 disposant d'une clé primaire id. Dans la table ma_table2, on va avoir un attribut mon_attr qui doit faire référence à id.
create table ma_table2(...,mon_attr int,...,foreign key(mon_attr) references ma_table1(id));
Attention, il faut bien séparer la déclaration de l'attribut de la contrainte de clé étrangère. Ici je définis d'abord mon_attr, puis ensuite je le déclare comme clé étrangère.

  1. create table personne(id int primary key, nom varchar(20), adresse varchar(20));
  2. create table cours(cnum int primary key, descr varchar(20));
  3. create table affectation(id int, cnum int, note int, primary key(id,cnum),foreign key(id) references personne(id), foreign key(cnum) references cours(cnum));