MySQL effectue des transactions ACID et l'intégrité référentielle

Toute application qui inclut une base de données doit respecter les caractéristiques ACID, dans les applications et les gestionnaires de bases de données professionnels le concept ACID fait référence aux caractéristiques ou propriétés qui garantissent que les transactions dans les bases de données s'effectuent en toute sécurité et confiance. Plus précisément, ACID signifie Atomity, Cohérence, Isolation et Durabilité.

Une transaction au sein d'une base de données consiste, par exemple, à insérer un enregistrement ou, si nous le considérons comme un modèle commercial, à ajouter un nouveau client, à modifier un produit. Les transactions produisent toujours un changement, insérer, modifier, supprimer, une requête n'est pas une transaction puisqu'elle ne produit pas de changements.
Spécification des propriétés ACID

Atomicité

C'est la propriété qui garantit et vérifie si la transaction a été effectuée ou non et cette propriété indique que si une opération n'est pas effectuée complète alors elle est annulée, par exemple supposons que l'on insère un enregistrement et que le serveur plante, un enregistrement est enregistré au milieu, alors en raison de l'atomicité cet enregistrement ne sera pas enregistré.
Un autre exemple si un paiement en ligne est effectué et que le montant est déduit de notre compte mais que le paiement échoue ou que le système plante, alors la transaction est annulée et la base de données ne l'enregistre pas.

Cohérence

C'est la propriété qui garantit que les transactions qui peuvent se terminer sans problème seront exécutées. Ce concept a à voir avec l'intégrité de la base de données. Cela empêche les données d'être modifiées et de perdre leur sens ou d'être sans aucune référence, par exemple, un client ne peut pas être supprimé alors qu'il a effectué un achat à un moment donné. Si vous souhaitez supprimer le client, vous devez d'abord supprimer toutes les factures et données liées à ce client.

Isolation

C'est la propriété qui garantit que si deux ou plusieurs transactions se produisent en même temps, elles seront exécutées l'une après l'autre et si elles sont exécutées en parallèle, chacune le fera indépendamment l'une de l'autre pour éviter d'éventuelles erreurs.

Durabilité

C'est la propriété qui est chargée de garantir qu'une transaction a été effectuée et que les modifications apportées par la transaction sont permanentes, même en cas de problème tel que le manque d'électricité ou les pannes du système.
MySQL prend en charge le format InnoDB, qui est une forme de stockage de données pour MySQL, inclus en tant que format de table standard dans toutes les distributions MySQL. Ce format prend en charge les transactions de type ACID et l'intégrité référentielle.
Nous allons faire quelques exemples d'implémentation d'ACID avec Mysql, les requêtes pourront alors être implémentées dans n'importe quel langage de programmation. Dans cette base de données, chaque utilisateur disposera d'un niveau de privilèges et d'actions qu'il pourra exécuter dans le système de l'entreprise. Nous nous concentrerons uniquement sur cette fonctionnalité.
Nous allons commencer par créer une base de données SociétéDB à partir de phpmyadmin, nous allons créer une table d'utilisateurs et sélectionner le moteur de stockage InnoDB.

 - Structure de la table pour la table `users` CREATE TABLE IF NOT EXISTS` users` (`userid` int (10) NOT NULL,` name` varchar (150) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Nous ajoutons des données à la table des utilisateurs

 INSÉRER DANS `users` (` userid`, `name`) VALUES (1, 'Carlos Alberte'), (2, 'Pablo Callejos'), (3, 'Ana Bolena');

Dans ce cas nous créons la clé primaire de la table users qui sera userid

 - Index de la table `users` ALTER TABLE` users` ADD PRIMARY KEY (`userid`);

Ensuite, nous allons créer la table des niveaux

 - Structure de la table pour la table `levels` CREATE TABLE IF NOT EXISTS` levels` (`levelid` int (11) NOT NULL,` level` varchar (50) DEFAULT NULL) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Nous ajoutons des données à la table des niveaux

 INSERT INTO `levels` (` levelid`, `level`) VALUES (1, 'Basic'), (2, 'Intermediate'), (3, 'Advanced');

Nous créons ensuite le tableau des privilèges, où nous indiquerons le niveau de permissions de chaque utilisateur

 - Structure de la table pour la table `privilèges` CREATE TABLE IF NOT EXISTS` privilèges` (`idprivilege` int (11) NOT NULL,` idlevel` int (11) NOT NULL DEFAULT '0', `idusuario` int (11 ) NOT NULL ) MOTEUR = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = latin1;

Nous ajoutons des données à la table des privilèges

 INSERT INTO `privileges` (` privilegeid`, `levelid`,` userid`) VALUES (1, 1, 1), (2, 2, 3), (3, 1, 2);

Nous ajoutons ensuite les relations à partir de l'éditeur SQL, j'affecte une clé étrangère qui relie la table des privilèges et des utilisateurs via l'ID utilisateur et la clé étrangère qui relie les privilèges aux niveaux via l'ID de niveau.

 - Filtres pour la table `privileges` ALTER TABLE` privilèges` ADD CONSTRAINT `levellfk` FOREIGN KEY (` levelid`) REFERENCES `levels` (` levelid`), ADD CONSTRAINT `privilegesfk` FOREIGN KEY (` userid`) RÉFÉRENCES ` utilisateurs ` (`ID utilisateur`);

Ensuite, nous consultons la base de données pour voir si les données sont correctes

 SELECT utilisateurs.nom, niveaux.niveau FROM utilisateurs, niveaux, privilèges WHERE privilèges.idlevel = niveaux.levelid ANDusers.userid = privileges.userid

Voyons comment cela fonctionne ensuite, nous essayons d'insérer les privilèges à un utilisateur et à un niveau qui n'existent pas.
L'instruction sera la suivante INSERT INTO privilèges VALUES (privilegeid, userid, levelid); donc

 INSERT INTO privilèges VALUES (6, 8, 10);

Cela donne une erreur puisque la clé étrangère de userid dans la table des privilèges créerait une incohérence si on ajoutait un utilisateur qui n'existe pas dans la table des utilisateurs, ici on évite l'erreur, avec le format MonSam les données auraient été sauvegardées sans problème.
Ensuite, nous allons essayer de supprimer un utilisateur de la table des utilisateurs :

 DELETE FROM `users` WHERE userid = 3

Lors de l'exécution de l'instruction SQL, cela nous donnera une erreur, car le client ne peut pas être supprimé car il contient des données dans d'autres tables dans ce cas, le client avec l'ID 3 est dans la table des privilèges, si nous voulons le supprimer, nous devons d'abord le supprimer de toutes les tables et après la table des clients.

Pour supprimer ce type d'enregistrements avec des clés étrangères, celui appelé delete est utilisé dans CASCADE, dans lequel tous les enregistrements liés à une requête particulière seront supprimés dans toutes les tables où ils ont des relations de clé étrangère. Pour effectuer cette transaction, nous utilisons la fonction SUR SUPPRIMER CASCADE.
La première chose sera de donner l'autorisation de supprimer en cascade, rappelez-vous qu'au démarrage par défaut le type de référence est RESTREINDRE ce qui indique que les données de ce champ du tableau ne peuvent pas être mises à jour ou supprimées, c'est pour des raisons de sécurité, toute phrase exécutée ne pourra effectuer aucune transaction, nous modifions donc les autorisations de modification et de suppression.

 ALTER TABLE `privileges` ADD CONSTRAINT` privilegesfk` FOREIGN KEY (`userid`) REFERENCES` users` (`userid`) ON SUPPRIMER CASCADE ON UPDATE CASCADE;

Nous effectuons à nouveau la requête SQL

 DELETE FROM `users` WHERE userid = 3

Ensuite, nous pouvons effectuer la requête sql pour vérifier qu'il n'est plus dans aucune table :

 SELECT utilisateurs.nom, niveaux.niveau FROM utilisateurs, niveaux, privilèges O privilèges.idlevel = niveaux.idlevel ET utilisateurs.userid = privileges.userid

L'utilisateur 3 a également été supprimé de la table des utilisateurs et de la table des privilèges, car l'ID utilisateur y avait une clé étrangère.
C'est la même chose pour les mises à jour lors de la modification, il peut être mis en cascade pour maintenir l'intégrité référentielle dans Mysql et la relation entre les tables.
Voyons ce qui se passe si nous ajoutons des données incorrectes dans l'insertion de la chaîne, par exemple, nous ajoutons un utilisateur, mais lorsque nous ajoutons un privilège, nous obtenons le mauvais identifiant

 INSÉRER DANS LES VALEURS DES UTILISATEURS (5, 'Julia Montaña'); INSERT INTO privilèges (`privilegeid`,` levelid`, `userid`) VALUES (6, 2, 6);

Dans ce cas, l'utilisateur sera enregistré mais pas ses privilèges car l'ID 6 n'existe pas dans la table des utilisateurs.Avez-vous aimé et aidé ce tutoriel ?Vous pouvez récompenser l'auteur en appuyant sur ce bouton pour lui donner un point positif