Il existe différents types de contraintes
KEY est un synonyme de INDEX
Lorsqu’on ajoute un index sur une ou des colonnes, MySQL construit une table d’indexage, qui permet de trouver très rapidement tous les n-upplets dont l’index a une valeur donnée. Cela permet d’accélérer les recherches et les jointures mais augmente la taille sur le disque et en mémoire.
UNIQUE
Crée un index unique. Si on essaie d’ajouter un n-upplet avec une valeur de clé identique à un n-upplet existant, alors une erreur est levée. Un index unique peut être laissé null et il peut y avoir plusieurs n-upplets ayant une valeur nulle.
PRIMARY KEY
La clé primaire permet d’identifier de manière unique un n-upplet dans une table. Toutes les colonnes d’une clé primaire doivent être non nulles. La clé primaire devrait être relativement courte, pour éviter de surchager le moteur de stockage.
Dans la table créée, une PRIMARY KEY est placée en premier, suivie de tous les index UNIQUE, puis des index non uniques. Cela aide l’optimiseur MySQL à hiérarchiser l’index à utiliser et à détecter plus rapidement les clés UNIQUE dupliquées.
FOREIGN KEY
Les clés étrangères permet de lier un index dans la table en cours à un index dans une autre table. Cela permet de maintenir la cohérence des données. Par exemple, on ne peut pas ajouter un n-upplet dans la table commande en référençant une personne qui n’existe pas dans la table client.
CHECK
Permet de vérifier que la valeur donnée correspond à une des valeurs définies.
SHOW INDEX FROM nom_table [FROM database] [WHERE expr]
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX nom_index ON nom_table (attributs)
On peut ajouter des index en créant la table:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
OrderStatus tinyint(1) NOT NULL,
PRIMARY KEY (OrderID),
UNIQUE KEY OrderNumber (OrderNumber),
KEY OrderStatus (OrderStatus),
CONSTRAINT PersonID FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
En la modifiant:
ALTER TABLE Orders
ADD PRIMARY KEY (OrderID),
ADD UNIQUE OrderNumber (OrderNumber)
ADD INDEX OrderStatus (OrderStatus),
ADD CONSTRAINT PersonID FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Ou à part:
CREATE UNIQUE INDEX OrderNumber ON Orders (OrderNumber);
CREATE INDEX OrderStatus on Orders (OrderStatus);
DROP INDEX nom_index
Pour créer une clé étrangère, il faut
CREATE TABLE commande_produits (
id INT(11) PRIMARY KEY,
id_commande INT(11),
id_produit INT(11),
FOREIGN KEY(id_commande) REFERENCES commande (id) ON UPDATE SET NULL ON DELETE CASCADE,
FOREIGN KEY(id_produit) REFERENCES produit (id)
);
ALTER TABLE `category`
ADD CONSTRAINT `parent` FOREIGN KEY (`parent`) REFERENCES `category`(`id`)
ON DELETE CASCADE ON UPDATE RESTRICT;
On peut contrôler le comportement du n-upplet lorsque la valeur référencée est supprimée avec ON DELETE:
ON DELETE {RESTRICT | NO ACTION | SET NULL | CASCADE}
Ou lorsqu’elle est modifiée avec ON UPDATE:
ON UPDATE {RESTRICT | NO ACTION | SET NULL | CASCADE}
| Valeur | Description | Exemple |
|---|---|---|
| NO ACTION | (Comportement par défaut) Lever une erreur si l'on essaie de supprimer une référence Synonyme: RESTRICT |
Si l'on essaie de supprimer un groupe alors qu'un étudiant appartient à ce groupe, la suppression échoue en affichant une erreur (Cannot delete or update a parent row: a foreign key constraint fails [...]) |
| SET NULL | Mettre à NULL la valeur lorsqu'on supprime la référence |
Si l'on supprime une catégorie, on met NULL pour catégorie aux produits associés (= sans catégorie) |
| CASCADE | Supprimer la ligne si la référence est supprimée | Si l'on supprime un concert, on supprime toutes les réservations associées. |