Hoja de repaso: Gestion des clés et contraintes en SQL

📋 Plan du Cours

1. DDL en SQL
2. Création de tables
3. Clés primaires et étrangères
4. Contraintes d'unicité
5. Modification de tables
6. Types de données SQL
7. Types numériques
8. Types date et heure
9. Types chaîne de caractères
10. Intégrité référentielle

📖 1. DDL en SQL

🔑 Notions clés & Définitions

• Data Definition Language (DDL) : Langage permettant de définir et de gérer la structure d'une base de données, comprenant les commandes CREATE, ALTER, DROP.
• CREATE : Commande pour créer une nouvelle base, table ou autre objet dans la base de données.
• ALTER : Commande pour modifier la structure d'une table existante, comme ajouter ou supprimer des colonnes ou contraintes.
• DROP : Commande pour supprimer définitivement une base ou une table.
• Clé primaire (PRIMARY KEY) : Champ ou ensemble de champs garantissant l'unicité de chaque enregistrement dans une table.
• Clé étrangère (FOREIGN KEY) : Champ ou ensemble de champs établissant une relation avec la clé primaire d'une autre table, assurant l'intégrité référentielle.

📝 Points essentiels

• La DDL permet de créer, modifier et supprimer la structure des bases de données et des tables.
• La syntaxe CREATE TABLE définit les colonnes, leurs types, contraintes (clé primaire, étrangère, unicité).
• La commande ALTER TABLE sert à ajouter, supprimer ou modifier des colonnes et contraintes dans une table existante.
• La gestion des clés (primaire, étrangère) est essentielle pour assurer l'intégrité et la cohérence des données.
• Les contraintes de référentialité (ON DELETE, ON UPDATE) contrôlent le comportement lors de modifications ou suppressions de données liées.
• La définition précise des types de données (entiers, chaînes, dates, etc.) est cruciale pour la cohérence et la performance.

💡 À retenir

La DDL en SQL permet de structurer et de maintenir la cohérence de la base de données en créant et modifiant ses objets, notamment via la définition claire des clés et contraintes pour assurer l'intégrité des relations.

📖 2. Création de tables

🔑 Notions clés & Définitions

• CREATE TABLE : Instruction SQL permettant de créer une nouvelle table dans la base de données avec ses colonnes, contraintes et types de données.
• Clé primaire (PRIMARY KEY) : Champ ou ensemble de champs identifiant de manière unique chaque enregistrement d'une table. Elle ne doit pas contenir de valeurs nulles.
• Clé étrangère (FOREIGN KEY) : Champ ou ensemble de champs dans une table qui référence la clé primaire d'une autre table, assurant l'intégrité référentielle.
• Contraintes (CONSTRAINT) : Règles appliquées aux colonnes ou tables pour garantir la cohérence des données, comme l'unicité, la non-nullité ou la référence à une autre table.
• ALTER TABLE : Commande SQL permettant de modifier la structure d'une table existante (ajouter, supprimer ou modifier des colonnes ou contraintes).
• Types de données : Définissent la nature des valeurs stockées dans une colonne (ex : INT, VARCHAR, DATE, etc.).

📝 Points essentiels

• La création d'une table se fait via CREATE TABLE, en précisant colonnes, types, contraintes et clés.
• La clé primaire peut être définie directement lors de la création ou via une contrainte nommée.
• Les clés étrangères permettent de maintenir l'intégrité référentielle entre tables, avec options ON DELETE et ON UPDATE (CASCADE, SET NULL, etc.).
• La commande ALTER TABLE sert à ajouter, supprimer ou modifier des colonnes et contraintes après création.
• La gestion des contraintes nommées facilite leur modification ou suppression ultérieure.
• Types de données variés : numériques (INT, DECIMAL), chaînes (VARCHAR, CHAR), date/heure (DATE, TIMESTAMP), etc.

💡 À retenir

La création et la modification de tables en SQL reposent sur la définition précise des colonnes, types, clés et contraintes, garantissant la cohérence et l'intégrité des données dans la base.

📖 3. Clés primaires et étrangères

🔑 Notions clés & Définitions

• Clé primaire (Primary Key) : Champ(s) ou ensemble de champs qui identifient de manière unique chaque enregistrement dans une table. Elle ne doit pas contenir de valeurs nulles et doit être unique pour chaque ligne.

• Clé étrangère (Foreign Key) : Champ(s) dans une table qui référencent la clé primaire d'une autre table. Elle établit une relation entre deux tables et garantit l'intégrité référentielle.

• Intégrité référentielle : Règle assurant que les relations entre les tables sont cohérentes, c'est-à-dire que chaque clé étrangère doit correspondre à une clé primaire existante ou être null.

• Contrainte (Constraint) : Règle appliquée à une colonne ou un ensemble de colonnes pour garantir des propriétés comme l'unicité (UNIQUE), la non-nullité (NOT NULL), ou la référence (FOREIGN KEY).

• Relation : Association entre deux ou plusieurs tables basée sur des clés primaires et étrangères, permettant de modéliser des liens logiques dans la base de données.

📝 Points essentiels

• La clé primaire doit être unique et non nulle, elle sert à identifier de façon unique chaque enregistrement.
• La clé étrangère établit une relation entre deux tables en faisant référence à la clé primaire d'une autre table.
• La définition d'une clé étrangère peut inclure des actions lors de la suppression ou modification de la clé référencée (ex : CASCADE, SET NULL).
• La cohérence des données est assurée par l'intégrité référentielle, empêchant la création de références invalides.
• La syntaxe pour définir une clé étrangère peut se faire directement lors de la création de la table ou après via la commande ALTER TABLE.
• Lorsqu'une clé étrangère est composée de plusieurs colonnes (clé composite), la déclaration doit respecter cette composition.

💡 À retenir

Les clés primaires garantissent l'unicité des enregistrements, tandis que les clés étrangères assurent la cohérence et la liaison entre différentes tables, formant la base des relations dans une base de données relationnelle.

📖 4. Contraintes d'unicité

🔑 Notions clés & Définitions

• Contrainte d’unicité (UNIQUE) : restriction qui garantit que toutes les valeurs d’un ou plusieurs champs d’une table sont distinctes, empêchant les doublons.
• Clé primaire (PRIMARY KEY) : contrainte qui identifie de façon unique chaque enregistrement d'une table ; elle implique automatiquement une contrainte d’unicité.
• Contrainte d’unicité composite : contrainte d’unicité appliquée à plusieurs colonnes simultanément, garantissant que la combinaison des valeurs est unique.
• Nommage de contrainte : attribution d’un nom spécifique à une contrainte pour faciliter sa gestion (ajout, suppression).
• Point à retenir : La contrainte d’unicité assure l’intégrité des données en empêchant la duplication des valeurs dans une ou plusieurs colonnes, ce qui est essentiel pour maintenir la cohérence des données.

📝 Points essentiels

• La contrainte UNIQUE peut être ajoutée lors de la création ou modification d’une table pour garantir l’unicité d’un ou plusieurs champs.
• Elle peut s’appliquer à une seule colonne ou à une combinaison de colonnes (contrainte composite).
• Lorsqu’elle est appliquée à une seule colonne, elle garantit que chaque valeur est unique dans cette colonne.
• La contrainte PRIMARY KEY est une contrainte d’unicité stricte, mais elle ne peut exister qu’une seule par table.
• La gestion des contraintes nommées permet de faciliter leur suppression ou modification ultérieure.
• La violation d’une contrainte d’unicité lors de l’insertion ou de la mise à jour provoque une erreur, empêchant la duplication.

💡 À retenir

La contrainte d’unicité, qu’elle soit simple ou composite, est essentielle pour assurer l’intégrité et la cohérence des données en empêchant la duplication de valeurs dans une ou plusieurs colonnes.

📖 5. Modification de tables

🔑 Notions clés & Définitions

• ALTER TABLE : Instruction SQL permettant de modifier la structure d'une table existante, comme ajouter, supprimer ou modifier des colonnes ou contraintes.
• Ajout de colonne (ADD) : Opération pour insérer une nouvelle colonne dans une table existante, avec possibilité de définir ses contraintes (ex : NOT NULL, DEFAULT).
• Suppression de colonne (DROP) : Opération pour retirer une colonne d'une table, en supprimant ses données et sa définition.
• Contraintes nommées (CONSTRAINT) : Définition explicite d'une contrainte (clé primaire, étrangère, unicité) avec un nom spécifique, facilitant la gestion et la suppression ultérieure.
• Clé étrangère (FOREIGN KEY) : Contrainte qui lie une colonne à la clé primaire d'une autre table, assurant l'intégrité référentielle.
• Unicité (UNIQUE) : Contrainte garantissant que les valeurs d'une ou plusieurs colonnes soient uniques dans la table.

📝 Points essentiels

• La commande ALTER TABLE permet d'ajouter, supprimer ou modifier des colonnes et contraintes sans recréer la table.
• Lors de l'ajout d'une colonne, on peut spécifier des contraintes comme NOT NULL, DEFAULT, ou une clé étrangère.
• La suppression d'une colonne ou contrainte doit respecter l'intégrité référentielle, notamment en supprimant d'abord les contraintes nommées si nécessaire.
• La gestion des clés étrangères inclut des options de comportement lors de la suppression ou modification des données liées (ON DELETE CASCADE, ON UPDATE SET NULL, etc.).
• La création de contraintes nommées facilite leur gestion, notamment leur suppression ou modification ultérieure.

💡 À retenir

L'utilisation de ALTER TABLE permet une modification flexible et précise de la structure des tables, tout en assurant la cohérence des relations grâce aux contraintes nommées et aux options de gestion des référentiels.

📖 6. Types de données SQL

🔑 Notions clés & Définitions

• Type de données : Catégorie de valeur qu'une colonne peut contenir (ex : numérique, texte, date). Il définit la nature et le format des données stockées.
• Types numériques : Incluent INTEGER, SMALLINT, BIGINT, DECIMAL, FLOAT, DOUBLE. Utilisés pour stocker des nombres entiers ou à virgule.
• Types de chaînes : VARCHAR, CHAR, TEXT, SET. Utilisés pour stocker du texte ou des ensembles de valeurs.
• Types de date/heure : DATE, DATETIME, TIMESTAMP, TIME, YEAR. Gèrent les données temporelles.
• Types binaires : BLOB, VARBINARY. Pour stocker des données binaires comme des images ou fichiers.

📝 Points essentiels

• La sélection du type de données influence la taille, la performance et l'intégrité des données.
• Les types numériques ont des plages de valeurs spécifiques, notamment BIGINT pour de très grands nombres.
• Les types de chaînes comme VARCHAR sont variables, tandis que CHAR est fixe.
• Les types de date/heure permettent de gérer précisément les données temporelles, avec des plages différentes.
• La compatibilité des types avec la base de données est cruciale pour éviter des erreurs ou pertes de données.
• La précision des types numériques (ex : DECIMAL(5,2)) est essentielle pour des calculs financiers précis.

💡 À retenir

Les types de données SQL déterminent la nature, la taille et la précision des données stockées, impactant la performance et l'intégrité de la base. Choisir le bon type est essentiel pour une gestion efficace des données.

📖 7. Types numériques

🔑 Notions clés & Définitions

• Type numérique : Catégorie de types de données permettant de stocker des valeurs numériques, entières ou décimales, dans une base de données.
• INT (Integer) : Type entier permettant de stocker des nombres entiers dans une plage spécifique, selon la taille (ex : TINYINT, SMALLINT, BIGINT).
• DECIMAL / NUMERIC : Types pour stocker des nombres à virgule fixe avec une précision définie (ex : decimal(5,2) pour 3 chiffres avant et 2 après la virgule).
• FLOAT / DOUBLE : Types à virgule flottante pour représenter des nombres décimaux approximatifs, avec une précision variable.
• BIT : Type pour stocker des valeurs binaires (0 ou 1), utilisé pour représenter des booléens ou des flags.

📝 Points essentiels

• Les types entiers (TINYINT, SMALLINT, INT, BIGINT) ont des plages de valeurs différentes, adaptées à la taille et à la précision requise.
• Les types décimaux (DECIMAL, NUMERIC) sont précis et utilisés pour des valeurs monétaires ou nécessitant une exactitude.
• FLOAT et DOUBLE sont utilisés pour des calculs nécessitant une approximation, avec une précision limitée.
• La sélection du type numérique doit tenir compte de la plage de valeurs attendues et de la précision nécessaire.
• Les types numériques peuvent être signés ou non (unsigned), ce qui influence la plage de valeurs possibles.
• La taille (M) ou la précision (P) est souvent spécifiée pour optimiser l'espace de stockage.

💡 À retenir

Les types numériques en SQL permettent de stocker efficacement des valeurs entières ou décimales, en choisissant la précision et la taille adaptées à chaque besoin, garantissant ainsi l'intégrité et la performance des données.

📖 8. Types date et heure

🔑 Notions clés & Définitions

• DATE : Type de donnée qui stocke une date au format année-mois-jour (YYYY-MM-DD). Exemple : 2024-04-27.
• DATETIME : Combinaison de date et d’heure, formatée en YYYY-MM-DD HH:MM:SS. Exemple : 2024-04-27 14:30:00.
• TIMESTAMP : Représente un instant précis en secondes depuis l'époque Unix (1970-01-01 00:00:01 UTC). Utilisé pour suivre la date de modification.
• TIME : Type de donnée pour stocker une heure (heures, minutes, secondes), avec une plage allant jusqu’à ±838 heures. Exemple : 15:45:30.
• YEAR : Type pour stocker une année sur 4 chiffres, généralement entre 1000 et 9999. Exemple : 2024.
• INTERVAL : Représente une période de temps, composée de plusieurs champs entiers (jours, heures, minutes, etc.), selon le type d’intervalle.

📝 Points essentiels

• Les types de date et heure permettent de stocker, manipuler et comparer des valeurs temporelles dans la base.
• La précision et la plage varient selon le type : DATE pour une date simple, DATETIME pour date+heure, TIMESTAMP pour un instant précis basé sur l’UTC.
• La gestion des fuseaux horaires est implicite dans TIMESTAMP (converti en UTC), contrairement à DATETIME.
• La compatibilité avec différents systèmes de gestion de bases de données nécessite de choisir le type adapté à l’usage (ex : suivi de modifications avec TIMESTAMP, stockage de dates avec DATE).

💡 À retenir

Les types de date et heure permettent de gérer efficacement le temps dans une base, en choisissant le format et la précision adaptés à chaque besoin spécifique.

📖 9. Types chaîne de caractères

🔑 Notions clés & Définitions

• VARCHAR : Type de donnée pour stocker des chaînes de caractères de longueur variable, jusqu’à une limite maximale spécifiée (M). Exemple : VARCHAR(100) stocke jusqu’à 100 caractères.
• CHAR : Type pour chaînes de longueur fixe, toujours rempli avec des espaces si la chaîne est plus courte que la longueur définie. Exemple : CHAR(10).
• SET : Type permettant de stocker une collection de valeurs prédéfinies, pouvant contenir zéro, une ou plusieurs valeurs parmi une liste définie.
• BLOB : Binary Large Object, utilisé pour stocker des données binaires (images, fichiers), avec une capacité maximale de 65 535 octets.
• TEXT : Type pour stocker de longues chaînes de caractères, jusqu’à 65 535 caractères.
• String : Terme général désignant tout type de donnée textuelle en SQL, incluant VARCHAR, CHAR, TEXT, etc.

📝 Points essentiels

• La différence principale entre VARCHAR et CHAR réside dans la gestion de la longueur : variable pour VARCHAR, fixe pour CHAR.
• Les types VARCHAR et CHAR sont optimisés pour différentes situations : VARCHAR pour longueur variable, CHAR pour données de longueur constante.
• SET est utile pour représenter des options multiples, mais doit être défini lors de la création de la colonne.
• La gestion de la mémoire et la performance peuvent varier selon le type choisi : CHAR est plus rapide pour des longueurs fixes, VARCHAR est plus efficace pour des longueurs variables.
• Les types TEXT et BLOB sont utilisés pour de très longues données textuelles ou binaires, respectivement.

💡 À retenir

Les types chaîne de caractères en SQL permettent d’optimiser le stockage et la gestion selon la nature des données, en choisissant entre longueur fixe ou variable, ou entre texte et données binaires.

📖 10. Intégrité référentielle

🔑 Notions clés & Définitions

• Intégrité référentielle : Garantie que les relations entre les tables via les clés étrangères sont cohérentes, empêchant la création ou la suppression de données qui laisseraient des références orphelines.

• Clé étrangère (Foreign Key) : Champ ou ensemble de champs dans une table qui référence la clé primaire d'une autre table, assurant la liaison entre les enregistrements.

• Référencement : La relation établie par une clé étrangère qui pointe vers une clé primaire dans une autre table, permettant de maintenir la cohérence des données.

• Actions en cas de modification (ON DELETE / ON UPDATE) : Définissent le comportement lors de la suppression ou de la mise à jour d’un enregistrement référencé, avec des options telles que CASCADE, SET NULL, NO ACTION, ou SET DEFAULT.

• Contrainte de unicité (Unique Constraint) : Garantit que les valeurs d’un ou plusieurs champs restent uniques dans une table, évitant les doublons dans les relations référentielles.

📝 Points essentiels

• L’intégrité référentielle empêche la création de références invalides, telles que des clés étrangères pointant vers des enregistrements inexistants.

• La gestion des actions (CASCADE, SET NULL, NO ACTION, SET DEFAULT) lors des opérations de suppression ou de modification est cruciale pour préserver la cohérence des données.

• La suppression d’un enregistrement référencé peut entraîner la suppression en cascade, la mise à null, ou l’interdiction selon la configuration des contraintes.

• La définition explicite de contraintes de clés étrangères et de contraintes d’unicité est essentielle pour assurer la cohérence des relations entre tables.

• La suppression ou la modification d’un enregistrement lié doit respecter la règle définie par la contrainte pour éviter les incohérences.

💡 À retenir

L’intégrité référentielle garantit la cohérence des données en assurant que toutes les références entre tables sont valides, grâce à la définition précise des clés étrangères et des actions associées en cas de modification ou suppression.

📊 Tableaux de Synthèse

Aspect	Création de tables	Clés primaires et étrangères
Commande principale	CREATE TABLE	PRIMARY KEY, FOREIGN KEY
Définition	Colonnes, types, contraintes	Unicité, relation entre tables
Modification	ALTER TABLE	Ajout/suppression de clés, modification de contraintes
Contraintes principales	NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY	Garantissent l’unicité et l’intégrité référentielle
Types de données	INT, VARCHAR, DATE, DECIMAL, etc.	Définissent la nature des données

Aspect	Contraintes d’unicité	Modification de tables
Objectif	Empêcher la duplication de valeurs	Ajouter, supprimer ou modifier colonnes ou contraintes
Contrainte principale	UNIQUE, PRIMARY KEY	ALTER TABLE pour ajuster la structure
Unicité composite	Sur plusieurs colonnes	Modifier une colonne, ajouter ou supprimer constraints
Nommage	Contrainte nommée pour gestion facile	Nécessaire pour gestion précise des contraintes
Impact	Maintien de la cohérence des données	Flexibilité dans la gestion de la structure

⚠️ Pièges & Confusions Fréquentes

1. Confondre PRIMARY KEY et UNIQUE : la PRIMARY KEY ne peut exister qu’une seule par table, tandis que UNIQUE peut être multiple.
2. Oublier que la clé étrangère doit référencer une clé primaire existante dans une autre table.
3. Ne pas spécifier NOT NULL sur une colonne clé primaire, ce qui provoque une erreur.
4. Définir une clé étrangère sans gérer les actions ON DELETE ou ON UPDATE, menant à des incohérences.
5. Utiliser des types de données inadaptés pour les clés (ex : VARCHAR pour une clé primaire numérique).
6. Omettre de nommer explicitement une contrainte, rendant sa gestion ultérieure difficile.
7. Modifier une table sans vérifier l’impact sur les relations existantes ou les contraintes.

✅ Checklist Examen

• Vérifier la compréhension de la syntaxe CREATE TABLE et ses composants.
• Savoir définir une clé primaire lors de la création d’une table.
• Connaître la différence entre PRIMARY KEY et UNIQUE.
• Être capable d’ajouter une clé étrangère avec ALTER TABLE.
• Comprendre le rôle de l’intégrité référentielle et comment la garantir.
• Savoir modifier une table existante avec ALTER TABLE.
• Identifier les types de données appropriés pour chaque colonne.
• Connaître les contraintes d’unicité et leur impact.
• Savoir gérer les contraintes nommées pour leur modification ou suppression.
• Vérifier la maîtrise des actions possibles lors de la suppression ou modification de clés étrangères (CASCADE, SET NULL).
• S’assurer de la cohérence entre types de données et contraintes.
• Vérifier la compréhension des contraintes d’intégrité et leur rôle dans la cohérence des données.