Mysql et SQL : table récursive et auto incrémentation de la clé primaire

• Objet : Créer une table récursive et y insérer des données
• Niveau requis :

  avisé
• Commentaires : créer un script de création et d'insertion pour une table récursive
• Prérequis : Installer mysql/mariadb

Soit la base de donnée nommée Transport qui a été créée : voir https://debian-facile.org/utilisateurs:hypathie:tutos:mysql-utiliser-l-objet-adresse-villes_departements_regions_de_france?&#script-creation-base-de-donnees-mysql-et-utilisateur

• Arborescence souhaitée :

Création de la table récursive nommée TransportRecursif pour une base de données SQL ou MYSQL.

SQL_Transport.sql

--drop database Transport
 
--create database Transport
--go
 
use Transport
go
 
IF OBJECT_ID('TransportRecursif') IS NOT NULL
    DROP TABLE TransportRecursif;
 
 
CREATE TABLE TransportRecursif(
        idTransport			Int IDENTITY(1,1) NOT NULL,
        typeTransport		Varchar (25) NOT NULL
        --commentTransport	Text,
        --id_type_pere		Int,
        --PRIMARY KEY (idTransport),
        --CONSTRAINT FK_id_transport_type_pere FOREIGN KEY (id_type_pere) REFERENCES TransportRecursif(idTransport)
);
 
INSERT INTO TransportRecursif (typeTransport)       VALUES ('Transport');
DELETE TransportRecursif;
TRUNCATE TABLE TransportRecursif;
DBCC CHECKIDENT ('TransportRecursif', RESEED, 0);
 
ALTER TABLE TransportRecursif ADD commentTransport	Text;
ALTER TABLE TransportRecursif ADD id_type_pere		Int;
ALTER TABLE TransportRecursif ADD PRIMARY KEY (idTransport);
ALTER TABLE TransportRecursif ADD CONSTRAINT FK_id_transport_type_pere FOREIGN KEY (id_type_pere) REFERENCES TransportRecursif(idTransport);
 
 
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Transport', NULL);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Transport', NULL);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Terrestre', 0);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Marin', 0);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Aérien', 0);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Voiture', 0);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Camion', 1);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Moto', 1);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Vélo', 1);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Hélico', 3);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Avion', 3);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('ULM', 3);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Fusée', 3);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Parachute', 3);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Planeur', 3);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Voilier', 2);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Paquebot', 2);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Planche à voile', 2);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Moto_Roadster', 6);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Moto_Side-car', 6);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Chasse', 9);
INSERT INTO TransportRecursif (typeTransport, id_type_pere)       VALUES ('Boeing_747', 9);

MYSQL_Transport.sql

use Transport;
 
DROP TABLE IF EXISTS `TransportRecursif`;
 
CREATE TABLE `TransportRecursif`(
        `idTransport`         Int AUTO_INCREMENT NOT NULL,
        `typeTransport`       Varchar (25) NOT NULL,
        `commentTransport`  Text,
        `id_type_pere`      Int,
        PRIMARY KEY  (`idTransport`),
        CONSTRAINT FK_id_transport_type_pere FOREIGN KEY (`id_type_pere`) REFERENCES TransportRecursif(`idTransport`)
);
 
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE `TransportRecursif`;
DELETE from `TransportRecursif`;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
INSERT INTO `TransportRecursif` (`idTransport`, `typeTransport`, `id_type_pere`) VALUES (0, 'Transport', NULL);
SET FOREIGN_KEY_CHECKS=1;
 
INSERT INTO `TransportRecursif` (`typeTransport`, `id_type_pere`) VALUES
('Terrestre', 0),
('Marin', 0),
('Aérien', 0),
('Voiture', 0),
('Camion', 1),
('Moto', 1),
('Vélo', 1),
('Hélico', 3),
('Avion', 3),
('ULM', 3),
('Fusée', 3),
('Parachute', 3),
('Planeur', 3),
('Voilier', 2),
('Paquebot', 2),
('Planche à voile', 2),
('Moto_Roadster', 6),
('Moto_Side-car', 6),
('Chasse', 9),
('Boeing_747', 9);

• On obtient après s'être connecté à la base de donnée Transport:

mysql> select idTransport, typeTransport, id_type_pere from TransportRecursif;

+-------------+------------------+--------------+
| idTransport | typeTransport    | id_type_pere |
+-------------+------------------+--------------+
|           0 | Transport        |         NULL |
|           1 | Terrestre        |            0 |
|           2 | Marin            |            0 |
|           3 | Aérien           |            0 |
|           4 | Voiture          |            0 |
|           5 | Camion           |            1 |
|           6 | Moto             |            1 |
|           7 | Vélo             |            1 |
|           8 | Hélico           |            3 |
|           9 | Avion            |            3 |
|          10 | ULM              |            3 |
|          11 | Fusée            |            3 |
|          12 | Parachute        |            3 |
|          13 | Planeur          |            3 |
|          14 | Voilier          |            2 |
|          15 | Paquebot         |            2 |
|          16 | Planche à voile  |            2 |
|          17 | Moto_Roadster    |            6 |
|          18 | Moto_Side-car    |            6 |
|          19 | Chasse           |            9 |
|          20 | Boeing_747       |            9 |
+-------------+------------------+--------------+