create table. Cette commande est complexe, aussi nous allons découper les arguments qu'elle peut prendre en plusieurs parties.
Le premier argument que prend cette commande est le nom de la table, qui doit être unique au sein d'un espace de travail.
Le deuxième argument que prend cette commande est composé de deux parties :
primary key. Toute table d'une base de données se doit de posséder une clé primaire, même si cela n'est pas obligatoire. Il ne peut y avoir qu'une seule colonne portant la contrainte
primary key, bien qu'il soit possible de regrouper plusieurs colonnes dans la déclaration d'une clé primaire. Cette contrainte implique deux choses pour les valeurs de cette colonne : il ne peut pas y avoir de valeur nulle, et il ne peut y avoir de doublons.
foreign key references. Cette contrainte prend un paramètre de type
table(colonne), qui désigne quelle colonne de quelle table est référencée par cette colonne. On appelle cette contrainte
une contrainte d'intégrité référentielle
. Souvent la colonne référencée par la clé étrangère est elle-même clé primaire dans sa table, mais ce n'est pas une obligation.
null ou
not null. Cette contrainte impose que les valeurs portées par cette colonne
peuvent
être nulles, ou au contraire
ne doivent pas
être nulles. Comme par défaut les valeus portées par une colonne peuvent être nulles, il est assez rare de rencontrer la contrainte
null. En revanche, la seconde contrainte est très fréquente.
unique indique que les doublons ne sont pas acceptés dans cette colonne.
default. Cette contrainte prend un paramètre, qui indique la valeur par défaut des cellules de cette colonne. Cette valeur est donc utilisée dans tous les cas où la valeur de cette cellule n'est pas précisée lors de la création d'une ligne.
check. Cette contrainte est très puissante et permet de valider les valeurs insérées.
collate. Cette contrainte est utilisée sur les colonnes qui portent des chaînes de caractères, et indique comment ces chaînes doivent être comparées les unes avec les autres. La
collation
de chaînes de caractères représente l'action de les comparer ou de les trier. Ce problème est très simple lorsque l'on se limite aux lettres minuscules non accentuées, et peut devenir rapidement très complexe lorsque l'on prend en compte les accents (aussi appelés diacritiques) et les différences minuscules / majuscules. L'utilisation de cette contrainte permet d'indiquer quelles méthodes de comparaison vont être utilisées. Nous n'irons pas plus loin sur ce point.
primary key. Cette contrainte prend en paramètre une liste de colonnes, et indique que la combinaison des valeurs doit constituer une clé primaire pour la ligne considérée.
foreign key : indique que le groupe de colonnes en paramètres référence le groupe de colonne en paramètre du
references qui doit suivre. Les colonnes ou groupe de colonnes référencées ne sont pas obligatoirement des clés primaires, la seule contrainte nécessaire sur elle est
unique.
unique. Cette contrainte prend en paramètre une liste de colonnes, et indique que la combinaison des valeurs de ces colonnes pour une ligne donnée doit être unique.
check : indique un certain nombre de règles qui doivent être vérifiées sur un groupe de colonnes.
Exemple 19. Table avec clé primaire et nom unique
create table country ( id int primary key, name varchar(32) unique not null ) ;
ID, qui constitue sa clé primaire. Il est décrit par un nom, qui doit être unique, et non nul.
Exemple 20. Table avec clé primaire composite, étrangère, contrainte
check
create table team (
country_id int not null references country(id) ,
id int not null,
name varchar(32) unique not null,
constraint pk_team primary key (id, country_id)
) ;
country_id, déclaré comme clé étrangère sur la colonne
id de la table
country.
Il possède une déclaration de contrainte sur une table : une clé primaire composite, sur les colonnes
id et
country_id. Une clé primaire déclarée sur plusieurs colonnes est appelée
clé primaire composite
.
Exemple 21. Table avec contrainte
check complexe
create table player (
country_id int not null references country(id) ,
id int not null,
team_id int references team(id),
family_name varchar(32) not null,
first_name varchar(32) not null,
retired int not null default 0 check (retired = 1 or retired = 0),
birth_year int not null check (birth_year > 0),
prof_year int not null check (prof_year > birth_year),
constraint pk_player primary key (id, country_id)
) ;
player. Notons qu'un joueur peut appartenir à une équipe, mais que cette équipe ne fait pas partie de la clé primaire. Effectivement, un joueur en inter-contrat ne fait partie d'aucune équipe, donc ce champ doit pouvoir être nul, il ne peut donc pas participer à la clé primaire. De plus, ce champ verra certainement sa valeur varier, et ce n'est jamais une bonne idée de faire varier la clé primaire d'une entité.
Enfin, notons la déclaration de la contrainte sur la valeur
retired, qui prend la valeur 0 par défaut, et ne peut prendre que les valeurs 1 ou 0.
alter table suivi du nom de la table.
Notons enfin que certaines opérations peuvent être assez délicates. Par exemple, ajouter une colonne à une table qui contient des lignes remplira cette colonne automatiquement avec des valeurs nulles. Si cette colonne doit finalement porter la restriction
not null, les choses doivent être faites en plusieurs étapes.
alter table. Dans l'exemple suivant on ajoute une colonne
capital_name à notre table
country.
Exemple 22. Ajout d'une colonne à la table
country
alter table country
add column capital_name varchar(32) ;
Exemple 23. Ajout d'une contrainte
alter table country
add constraint unique_name_capital unique(name, capital_name) ;
Exemple 24. Ajout d'une contrainte de type clé étrangère
alter table player
add constraint FK_player_team foreign key(team_id)
references team(id) ;
not null à l'aide de la commande
alter table ... add constraint. Ajouter cette contrainte se fait à l'aide de la syntaxe suivante.
capital_name de notre table
country.
unique_name_capital existe toujours, mais qu'elle ne référence plus la colonne que l'on vient de supprimer.
unique_name_capital que nous avons créée sur la table
country.
Exemple 27. Effacement d'une contrainte
-- version SQL standard alter table country drop constraint unique_name_capital ; -- version MySQL alter table country drop index unique_name_capital ;
drop. Pour effacer la table
player il suffit donc de taper le code suivant.
restrict, l'effacement est interdit, en mode
cascade, il se propage.
Dans le cas de notre exemple de clé entre la table
Marins et la table
Communes, on ne pourra effacer une commune référencée si la clé étrangère a été déclarée en mode
restrict, en revanche, tous les marins nés dans cette commune seront effacés si la clé a été déclarée en mode
cascade.
Exemple 29. Déclaration d'une clé étrangère en mode
restrict
alter table marins
add constraint foreign key FK_communes (id_commune)
references communes(id) on delete restrict ;
restrict ou
cascade peut être déclaré sur les opérations de délétion (
delete) et de mise à jour (
update).