# Create table Créer une table consiste de l’expression `create table` suivi par le nom de la table et ensuite, entre parenthèses, la liste **d’attributs** et leurs **contraintes**. ```sql create table films ( film_id int, title varchar(255), rental_rate numeric(5,2), primary key (film_id) ); ``` En créant nos tables, on essaie au maximum d'assurer les domaines exprimés dans notre schéma avec un objectif ultime : **assurer l'intégrité de nos données** ! * On ne veut pas de valeurs non valides dans la base * On veut forcer la présence de certaines valeurs, ou savoir explicitement quand il y a des valeurs manquantes * On veut forcer l'unicité de certaines valeurs * Quand il y a un lien entre deux tables, il faut que ce lien soit assuré et qu'il n'est pas *cassable* par erreur ! On assure l'intégrité de nos données via les **contraintes**. ## Intégrité : Contrainte par type de données Nous imposons une première contrainte sur le **type** de chaque attribut (ou colonne) de notre table. Cette contrainte est la première façon d'implémenter la contrainte sur le *domaine* des valeurs possibles pour un attribut. | Type | Description | | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | char(n) | Une chaîne de caractères de longueur fixe. char(8) stockera toujours 8 caractères, même si la valeur qu’on passe en a moins (des espaces seront ajoutés à la fin). | | varchar(n) | Une chaîne de caractères de longueur variable. | | text | Une longue chaîne de caractères plus adaptés aux paragraphes etc. | | int | Valeurs entières | | numeric(p,d) | Valeurs avec un point décimal fixe : p digits en total, avec d digits après le point. Ex. numeric(3,2) peut stocker la valeur 2.45 mais pas 24.45 | | real | Valeurs décimales avec la précision dépendant de l’architecture de la machine | | double | Valeurs décimales avec double précision dépendant de l’architecture de la machine | | float(n) | Une valeur décimale avec une précision de n digits. | | boolean | Une binaire | | date | Une date | | datetime | Une date et heure | {% hint style="info" %} Cette liste n’est pas exhaustive, et peut changer selon le type de SGBDR choisi. En général, les implémentations de MySQL supporte une liste générique de types : MariaDB publie une liste de types supportés explicitement par leur SGDBR : {% endhint %} Testons notre table, en ajoutant quelques lignes, avec la commande `insert into` : ```sql insert into films (film_id, title, rental_rate) values (45, "Gone with the wind", 5.99); ``` La commande `insert into` a la syntaxe suivant : ``` insert into ( liste d’attributs à insérer ) value (la liste de valeurs correspondantes) ``` On n’est pas obligé d’inclure tous les attributs d’une table : ```sql insert into films (film_id, title) values (66, "Les bronzés font du ski"); ``` Soit une valeur `default` est inséré à la place de l'attribut, manquant, soit `NULL` : | film\_id | title | rental\_rate | | -------- | ----------------------- | ------------ | | 45 | Gone with the wind | 5.99 | | 66 | Les bronzés font du ski | NULL | ### Exercice Écrivez et testez la création d’une table pour votre entité **Repas** de l’exercice précédente. * Vous utilisez quel type de donnée pour stocker les textes écrit par l’utilisateur ? * Vous utilisez quel type de donnée pour stocker une image ? Testez l’insertion de quelques données. ## Intégrité : Contraintes sur le contenu des colonnes Par défaut, chaque attribut autorise les valeurs **nulles**. On peut encore limite les domaines d'une colonne en enlevant la possibilité des **nulles**, préciser l’unicité des valeurs sur la table entière, ou bien contraindre les valeurs possibles via une condition. Il y a plusieurs sortes de contraintes sur les colonnes : * `not null` : les valeurs nulles ne sont pas autorisées * `unique` : les valeurs doivent être uniques sur toutes les lignes de la table * `check` : valider une donnée selon un critère Il existe aussi un opérateur `default` qui permet de fluidifier la contrainte `not null`, en fournissant une valeur par défaut dans l'absence d'une vraie valeur. L'exemple suivant démontre les différentes contraintes, ainsi que l'utilisation de `default` (ici, c'est une table pour la gestion des colis à livrer) : ```sql create table package ( package_id int NOT NULL UNIQUE, name varchar(256) NOT NULL default 'Unknown package', price decimal(8,2) NOT NULL default 0, article_count int default 1 CHECK (article_count > 0), date_of_creation date CHECK (date_of_creation like '__/__/____'), primary key (package_id) ); ``` {% hint style="warning" %} **`null` vs `not null` vs `default`** Attention au piège de `default` ! On risque de changer le sens d'un attribut en fournissant une valeur `default` : * Est-ce qu'il faut être averti de l'absence d'une information pendant la saisie ? Si oui, il faut utiliser `not null` seul (sans `default`). Le SGBDR va lancer une exception, et aucune information inconsistante sera conservée. * Est-ce qu'il faut savoir explicitement quand il a l'absence d'une information ? Si oui, il faut autoriser les `null` (sans `default`). On peut ensuite extraire des lignes avec des infos manquantes avec une phrase `select` et la condition `is null`, par exemple. En revanche, si on précise `default` dans le deux cas dessus, on risque de ne pas savoir si une information a été manquante ou pas, ni extraire des lignes qui manquent une information. On ne peut pas distinguer entre une valeur précisée par `default` d'une valeur saisie par l'utilisateur. {% endhint %} ### Exercice Copiez et exécutez la DDL de cette table « package » * Essayez des instructions de type « INSERT » * Sans « name », sans « price » ? * Avec article\_count = 0, < 0 ? * Sans date\_of\_creation ? Est-ce que CHECK suffit à la place de NOT NULL ? ## Intégrité : Contraintes par la clé primaire Nous savons déjà qu'une **clé primaire** s'agit de notre choix de clé parmi les **clés candidates** de notre entité. Cette clé doit avoir la caractéristique de pouvoir identifier une ligne unique parmi toutes les lignes de la table. Désignant une clé primaire, alors, impose implicitement plusieurs contraintes d'intégrité sur la ou les colonnes de la clé : * **unique** : les valeurs dans les attributs de la clé primaire doivent être uniques sur toute la table * **not null** : les valeurs ne peuvent pas être nulls * **indexation** : un index est construit sur ces attributs (notamment pour garantir l'unicité) Testons ces règles avec notre table **film**, en essayant d'ajouter une ligne sans préciser la clé primaire : ```sql insert into films (title) values ("Jurassic Park"); ERROR 1364 (HY000): Field 'film_id' doesn't have a default value ``` Nous avons imposé une **contrainte** type `primary key` sur la colonne **film\_id**, sans spécifier de valeur `default`. Nous aurons donc l'erreur affichée. Rappelez, une clé candidate peut se composer de plusieurs attributs, et donc en SQL, il est possible de créer une clé primaire composée : ```sql create table film_actor ( film_id int NOT NULL, actor_id int NOT NULL, primary key (film_id, actor_id) ); ``` ## Exercice Essayez d'ajouter plusieurs combinaisons de lignes à **film\_actor** * Avec des valeurs `null` * Avec 2 fois le même **film\_id**, mais des **actor\_id** différents * Avec 2 fois le même **film\_id** et **actor\_id** ## Intégrité référentielle En normalisation notre schéma, nous avons dû couper nos entités en deux. Les lignes avec un lien sémantique sont reliées par une ou plusieurs colonnes, qu'on appelle les **clés étrangères**. Rappelez l'exemple de la table **etudiant\_cours** et la table **cours** : | etudiant\_id | cours\_id | | ------------ | --------- | | 1 | SGBDR | | 1 | Backend | | 2 | Angular | | 3 | Backend | | 3 | Angular | | cours\_id | prix | | --------- | ---- | | SGBDR | 1000 | | Backend | 1500 | | Angular | 2000 | Ici, l'attribut **cours\_id** de la table **etudiant\_cours** est une *clé étrangère*, car il pointe vers la ligne correspondante dans la table **cours**. Ce lien est maintenu strictement par notre SGBDR : * on ne peut pas *supprimer* le cours *Angular*, par exemple, car la suppression créera une incohérence dans nos données. Que deviennent des étudiants qui à priori sont inscrits du cours Angular, alors que le cours Angular n'existe plus ? * on ne peut pas *ajouter* des étudiants qui prennent des cours qui ne se trouvent pas dans la table **cours**. Nous limitons le domaine possible via cette contrainte. Cette protection implicite dans notre SGBDR s'appelle **l'intégrité référentielle**. Mais, cela n'est pas automatique. Nous sommes obligés d'exprimer les clés étrangères manuellement : ```sql create table cours ( cours_id varchar(50) not null, prix decimal(8,2), primary key (cours_id) ); create table etudiant_cours ( etudiant_id int not null, cours_id varchar(50) not null, primary key(etudiant_id, cours_id), foreign key (cours_id) references cours(cours_id) ); ``` Nous avons ajouté la clause `foreign key...` : ```sql foreign key (attribut_de_cette_table) references autre_table (attribut_de_l’autre_table) ``` ### Exercice Insérez des données dans **cours** * Essayer d’insérer des lignes dans **etudiant\_cours** * Avec des noms des cours existants * Avec des noms des cours qui n’existent pas * Essayez de supprimer une ligne de cours avec la ligne suivante : ```sql /* Remplacez le ... par un cours_id d'un des cours existants dans cette table. */ delete from cours where cours_id = '...'; ``` {% hint style="info" %} À noter : **etudiant\_cours** est une entité faible, puisqu'une ligne ne peut pas être créée sans l’existence d’une valeur de **cours**. C’est grâce au `not null` sur **cours\_id** dans la création de **etudiant\_cours**. Si on autorise les valeurs nulles, **etudiant\_cours** peut exister dans la présence d'un cours, et donc devient une entité forte. {% endhint %} * Si pas déjà fait, insérer un cours avec un **cours\_id** de "SGBDR". Ajouter un étudiant à ce cours, en créant une ligne dans **etudiant\_cours**. * Essayez de modifier le nom du cours "SGBDR" à "DBMS". Qu'est-ce qu'il se passe ? ```sql update cours set cours_id = "DBMS" where cours_id = "SGBDR"; ``` {% hint style="success" %} L'intégrité référentielle est toujours assurée par le SGBDR ! Nous n'avons pas à faire de pre-tests ou de pre-vérifications de la validité des données. Notre SGBDR s'en occupe tout seul ! {% endhint %} ## Cascades Il existe des scénarios dans lesquels la modification ou suppression d’une ligne d’une table *parent* devrait faire modifier/supprimer les lignes dans les tables *enfants*. Pour continuer notre exemple, la personne qui a saisi le nouveau cours a fait une faute de frappe, et on a déjà des étudiants inscrits à ce cours. Comment corriger la faute si le SGBDR n'autorise pas la modification ? Par défaut, notre SGBDR impose des contraintes les plus fortes sur nos références par clé étrangère : * pas de suppression * pas de mise à jour Nous pouvons modifier ce comportement pour être plus souple, en précisant ce qu'il faut faire dans le cas d'une suppression ou modification d'une référence par clé étrangère. ```sql create table etudiant_cours ( ... foreign key (cours_id) references cours(cours_id) on delete cascade on update cascade ); ``` Ici, nous spécifions que faire dans le cas d'une suppression ou modification. Il y a trois possibilités : * `cascade` : faire une sorte de propager nos modifications dans les autres tables liées par la clé-étrangère. Par exemple, toutes les lignes dans **etudiant\_cours** avec "SGBDR" seront modifiés à "DBMS". * `set null` : Nous cassons les liens existants entre les deux tables, en mettant `null` dans la clé étrangère (seulement si autorisé) * `set default` : Affecter une valeur par défaut (en cassant aussi les liens) Si on oublie de préciser les règles `on delete` ou `on update` à la création initiale, il est bien possible de les ajouter plus tard. Nous serions obligés de supprimer la clé étrangère, et ensuite la recréer. Chaque clé (primaire ou étrangère) crée implicitement une contrainte dans notre SGBDR, qui lui dispose d'un identifiant. On peut récupérer toutes ses meta-données de nos tables avec la commande suivante : ```sql show create table etudiant_cours; ``` Le résultat retourné est : ```sql CREATE TABLE `etudiant_cours` ( `etudiant_id` int(11) NOT NULL, `cours_id` varchar(50) NOT NULL, PRIMARY KEY (`etudiant_id`,`cours_id`), KEY `cours_id` (`cours_id`), CONSTRAINT `etudiant_cours_ibfk_1` FOREIGN KEY (`cours_id`) REFERENCES `cours` (`cours_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ``` On voit dans le résultat la ligne : ```sql CONSTRAINT `etudiant_cours_ibfk_1` FOREIGN KEY (`cours_id`) REFERENCES `cours` (`cours_id`) ``` C'est la ligne qui met en place la contrainte par clé étrangère. Pour la modifier : ```sql /* D'abord, supprimer la clé étrangère */ alter table etudiant_cours drop foreign key etudiant_cours_ibfk_1; /* Ensuite la recréer */ alter table etudiant_cours add foreign key (cours_id) references cours(cours_id) on delete cascade on update cascade; ``` ### Exercice Ajoutez les options de cascade sur la clé étrangère sur **etudiant\_cours**. * En utilisant `update`, mettez à jour le nom de SGBDR à DBMS. Qu’est-ce qui se passe ? * En utilisant `delete`, essayez de supprimer la ligne SGBDR. Qu’est-ce qui se passe ? ## Intégrité vs Cascades vs Null vs Default Les cascades sont très pratiques pour assurer de l’intégrité automatique sur plusieurs tables. MAIS... le choix n’est pas facile ! S’il est important de garder un historique, les cascades ne sont pas nécessairement utiles : * un étudiant s’inscrit à un cours et il y participe. On facture sa participation au cours. L’étudiant quitte l’école. Il faut quand même le facturer le temps passé au cours… si on avait fait un `ON DELETE CASCADE` on n’aurait plus de trace de sa participation, et donc une facturation erronée Pour des données « temps réels » les cascades sont très utiles * un étudiant s’inscrit à un cours et il y participe. Il faut générer la feuille d’émargement L’étudiant quitte l’école. On ne veut plus voir l’étudiant sur la feuille d’émargement, donc un `ON DELETE CASCADE` serait pratique dans ce cas. Comment anticiper ces deux scenarii ? Une bonne analyse du domaine (il aura fallu identifier le cas de départ d'un étudiant, ainsi que la cas de facturation prorata), le suivi des procédures de normalisation, etc. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.glassworks.tech/sgbdr/data-definition-ddl/005-create-table.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.