--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);