CREATE TABLE

Asi me quedaron las tablas del reto propuesto 😃

CREATE TABLE `stations` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(`id`)
) 
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trains` (
  `serial_number` VARCHAR(10) NOT NULL, 
  `line_id` BIGINT(20) UNSIGNED NOT NULL,
  `type` TINYINT(4) NOT NULL,
  `year` INT(4) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(serial_number),
  CONSTRAINT `fk_line`
  FOREIGN KEY(`line_id`) REFERENCES `lines`(`id`)
)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

para hacer comillas invertidas en windows se hacen con alt+96

Importante el listado de caracteres (Charset) para que la base de datos pueda almacenar los caracteres latinos.
.
Hace mucho tiempo me paso en un proyecto, la aplicacion funcionaba (era .NET y usaba SQL) pero algunas busquedas fallaban.
.
Era el cotejado, el collate no se correspondia, luego de cambiarlo para caracteres latinos, todo funciono bien.

Me puse a averiguar los tipos de datos existentes en MySQL/MariaDB e hice un tutorial con esa información.

https://platzi.com/tutoriales/4203-mysql-mariadb/20970-tipos-de-datos-en-mysqlmariadb/

para los que tienen problemas con el codigo y les aparecen errores como este en windows:

PS C:\Users\xxxxxx\Desktop\FirstSteps\mariadb> mysql < name_file.sql
At line:1 char:7
+mysql < 2.create-tables.sql
The ‘<’ operator is reserved for future use.

To add the table before than log :

Get-Content name_file.sql | mysql -u user.example -p --database=database_name_ex;

• eso resolveria el problema del ‘<’

• pero si quieres crear la tabla desde adentro de la terminal de mariadb te recomiendo ejecutar todo el codigo adentro

1.logiar
2.USE database_name;
3.CREATE TABLE `lines` (
    `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR (10) NOT NULL,
    `color` VARCHAR (15) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)

)
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

por otro lado me di cuenta que la primary key a mi no me servia sin las `` , recomiendo agregarlas si tienes el mismo problema**

Si sigues com probemas te recomiendo revisar tus permisos como administrador:

SHOW GRANTS FOR 'nombre_de_usuario'@'localhost';

USE metro_cdmx;

CREATE TABLE `lines`

(`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(10) NOT NULL,
`color` VARCHAR(15) NOT NULL,

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(id)
)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trains`
(`serial_number` VARCHAR(10) NOT NULL,

`line_id`BIGINT(20) UNSIGNED NOT NULL,
`type` TINYINT(4) NOT NULL,
`year` INT(4) NOT NULL,

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(serial_number)
) 
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `stations`
(`id`BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(50) NOT NULL,

`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(id)
)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Necesito ayuda , cuando trato de hacer los pasos dentro de la terminal no me deja crear el archivo… me sale algo de que el operador ‘<’ esta reservado para uso futuro…

"
At line:1 char:7

• mysql < 2-create-tables.sql;

•   ~
  

The ‘<’ operator is reserved for future use.
+ CategoryInfo : ParserError: (😃 [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : RedirectionNotSupported
"

Ayudaaaaaaaaaa

El poder de CHAT-GPT

NO SE POR QUE NO MEDEJA HACER LAS TABLAS CON EL ERCHIVO, pero asi queda mi codigo

-- crear las tablas
USE metro_cdmx;

CREATE table `lines` (
    -- columnas de la tabla 
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `stations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(`id`)
) 
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trains` (
  `serial_number` VARCHAR(20) NOT NULL,
  `line_id` BIGINT UNSIGNED NOT NULL,
  `type` ENUM('moderno', 'viejo') NOT NULL,
  `year` YEAR NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`serial_number`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Así cree mis tables:

MariaDB [metro_cdmx]> show create table `lines`;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lines | CREATE TABLE `lines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `color` varchar(15) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [metro_cdmx]> show create table `trains`;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                     |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trains | CREATE TABLE `trains` (
  `serial_number` varchar(10) NOT NULL,
  `line_id` bigint(20) unsigned NOT NULL,
  `type` tinyint(4) NOT NULL,
  `year` int(4) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`serial_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [metro_cdmx]> show create table `stations`;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                     |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stations | CREATE TABLE `stations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

MariaDB [metro_cdmx]> show create table `locations`;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| locations | CREATE TABLE `locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` bigint(20) unsigned NOT NULL,
  `location` point NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.006 sec)

MariaDB [metro_cdmx]> ALTER TABLE `locations` MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.035 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [metro_cdmx]> show create table `locations`;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                              |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| locations | CREATE TABLE `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` bigint(20) unsigned NOT NULL,
  `location` point NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `update_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.011 sec)```

-- Crear las tablas

USE metro_cdmx;

CREATE TABLE IF NOT EXISTS `lines`(
    
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY(id)
)
DEFAULT CHARSET= utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stations`(
    
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(10) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY(id)
)

DEFAULT CHARSET= utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trains`(
    
    `serial_number` VARCHAR(10)  NOT NULL,

    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` TINYINT(4) NOT NULL,
    `year` INT(4) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY(serial_number)
)

DEFAULT CHARSET= utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Crear las tablas

USE metro;


-- ==========================================================================
-- Lines
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `lines` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Stations
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `stations` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Trains
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `trains` (
    
    `serial_number` VARCHAR(10) NOT NULL,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` TINYINT(4) UNSIGNED NOT NULL,
    `year` INT(4) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (serial_number),
    CONSTRAINT FK_trains_lines FOREIGN KEY (line_id) REFERENCES `lines`(`id`)
    
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Lines_stations
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `lines_stations` (
    
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `station_id` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT FK_lines_stations_lines FOREIGN KEY (line_id) REFERENCES `lines`(`id`),
    CONSTRAINT FK_lines_stations_stations FOREIGN KEY (station_id) REFERENCES `stations`(`id`)
    
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Locations
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `locations` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `station_id` BIGINT(20) UNSIGNED NOT NULL,
    `location` POINT NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT FK_locations_stations FOREIGN KEY (station_id) REFERENCES `stations`(`id`)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;



-- ==========================================================================
-- Drivers
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `drivers` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `status` BOOLEAN DEFAULT false,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;


-- ==========================================================================
-- Active Drivers
-- ==========================================================================

CREATE TABLE IF NOT EXISTS `active_drivers` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `driver_id` BIGINT(20) UNSIGNED NOT NULL,
    `status` BOOLEAN DEFAULT false,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT FK_active_drivers_drivers FOREIGN KEY (driver_id) REFERENCES `drivers`(`id`)
) 
ENGINE=INNODB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Para los que no pueden ejecutar el código en la terminal usando “<” Yo lo que hice fue copiar y pegar el código tal como lo hicimos para crear la database, pero en este caso para lines con todo el código. Más sencillo porque no encontré solución.

A mí me marcaba error los comentarios con doble guion: – Crear las tablas
Lo pude arreglar comentando de la siguiente manera:

/*Crear las tablas. 

Específicamos la base de datos a usar*/

💚 Para poder usar el archivo sin salir de la terminal de MariaDB, pueden usar el comando source. Para esta clase el comando quedaría, así como source 2-create-tables.sql.
Espero que les haya servido.

CREATE TABLE `trains` (
    `serial_number` BIGINT(20) UNSIGNED NOT NULL,

    `type` TINYINT(10) NOT NULL,
    `YEAR` INT(4) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (serial_number)
)
DEFAULT CHARSET=utf8mb4

CREATE TABLE `stations` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(50) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
)
DEFAULT CHARSET=utf8mb4

Dejo mi código con la Foreign Key de la tabla de trenes

Sólo un detalle, no necesitamos entrar a nuestro servidor de base de datos y seleccionar la base si en el script agregamos la instrucción para seleccionar la base, de la siguiente forma:

-- Crear las tablas

USE metro_cdmx;

CREATE TABLE `lines`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `color`VARCHAR(15) NOT NULL,
    `created_at`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id)
)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

“lines” es una palabra reservada. Tendran problemas si lo declaran como nombre de una tabla.

Si estás trabajando en Windows y te sale este error:

At line:1 char:18
+ mysql -u root -p < 2-create-tables.sql
+                  ~
The '<' operator is reserved for future use.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

En Windows PowerShell no soporta el operador de entrada ‘<’

En lugar de como indica el instructor en el video, usen:

Get-Content 2-create-tables.sql | mysql -u root -p

De aquí obtuve la información.

https://suay.site/?p=3223

Esto hice para la tabla “trenes” que lleva una llave foránea

CREATE TABLE `trains` (
   `serial_number` VARCHAR(10) NOT NULL,
   `line_id` BIGINT(20) UNSIGNED NOT NULL,
   `type` TINYINT(4) NOT NULL,
   `year` INT NOT NULL,
   `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   CONSTRAINT trains_pkey PRIMARY KEY(serial_number),
   CONSTRAINT line_fkey FOREIGN KEY(line_id)
     REFERENCES `lines`(id)
) 
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

mi código

USE metro_cdmx;

CREATE TABLE `estación` (

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)

)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trenes` (

    `serial_number` varchar(10) NOT NULL ,
    `line_id` BIGINT(20) SIN FIRMAR NO NULL,
    `tiny` INT(4) NOT NULL,
    `año` INT(4) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(serial_number)

)

DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

aquí los retos

USE metro_cdmx

CREATE TABLE `stations`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `trains`(
    `serial_number` BIGINT(20) NOT NULL,
    `type` TINYINT(4) NOT NULL,
    `year` INT(4) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (serial_number)
)CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Para el campo updated_at en mysql tendria que ser:

`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

para que se registre automaticamente la fecha y hora cada vez que se modifique el registro

Este es un aporte de un error, muy comun pero me paso, jajaja. Por favor guarden el documento antes de ejecutar, las comillas deben ser las back ` y no ', a mi me dio error.

USE metro_cdmx

DROP TABLE IF EXISTS `trains`;
DROP TABLE IF EXISTS `stations`;

CREATE TABLE `trains`(

    `serial_number` VARCHAR(10) NOT NULL,
    `line_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` TINYINT(4) NOT NULL,
    `year` INT(4) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (serial_number),
    CONSTRAINT `fk_train-line`
        FOREIGN KEY (line_id) REFERENCES `lines`(`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE `stations`(

    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id)
);

Solución al reto 😀

--Reto crear tablas de tations y drivers

USE metro_cdmx;

CREATE TABLE `stations`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10) NOT NULL,
    `color` VARCHAR(15) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
)
DEFAULT CHARSET =utf8mb4
COLLATE = utf8mb4_unicode_ci;


CREATE TABLE `drivers` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    `status` BOOLEAN NOT NULL DEFAULT FALSE,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
)
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

Reto en un archivo:

-- Crear las tablas stations y drivers

USE metro_cdmx;

CREATE TABLE `stations` (

  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(10) NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id)
)
DEFAULT CHARSET=utf8mb4;

CREATE TABLE `drivers` (

  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(10) NOT NULL,
  `status` BOOLEAN NOT NULL DEFAULT false,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id)
)
DEFAULT CHARSET=utf8mb4;

Estoy practicando con un proyecto que tengo en mente, así quedó mi tabla:

-- Crear la tabla

USE plantelesmx

CREATE TABLE `cctv2021` (

    `id_plantel` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

    `nombre_centro` CHAR(100) NOT NULL,

    `id_tipo` TINYINT(1) NOT NULL,

    `tipo_centro` CHAR(45) NOT NULL,

    `id_administrativa` TINYINT(2) NOT NULL,

    `nombre_estado` VARCHAR(8) NOT NULL,

    `bol_status` TINYINT(1) NOT NULL,

    `status` VARCHAR(8) NOT NULL,

    `fundacion` INT(5) NOT NULL,

    `alta_sis` VARCHAR(30) NOT NULL,

    `cambio_sis` VARCHAR(30) NOT NULL,

    `clausura` VARCHAR(30) NOT NULL,

    `reapertura` VARCHAR(30) NOT NULL,

    `reapertura_cambio` VARCHAR(30) NOT NULL,

    `vialidad_principal` TEXT NOT NULL,

    `vialidad_derecha` TEXT NOT NULL,

    `vialidad_izquierda` TEXT NOT NULL,

    `vialidad_posterior` TEXT NOT NULL,

    `num_exterior` INT(7) NOT NULL,

    `letra_exterior` VARCHAR(5) NOT NULL,

    `num_interior` INT(5) NOT NULL,

    `letra_interior` VARCHAR(5) NOT NULL,

    `id_estado` TINYINT(2) NOT NULL,

    `entidad_federativa` VARCHAR(35) NOT NULL,

    `id_mnpo` SMALLINT(3) NOT NULL,

    `municipio` VARCHAR(100) NOT NULL,

    `id_localidad` INT(4) NOT NULL,

    `localidad` VARCHAR(100) NOT NULL,

    `id_ambito` TINYINT(1) NOT NULL,

    `ambito` CHAR(6) NOT NULL,

    `id_asentamiento` INT(4) NOT NULL,

    `asentamiento` VARCHAR(200) NOT NULL,

    `id_tipo_asentamiento` INT(2) NOT NULL,

    `tipo_asentamiento` VARCHAR(25) NOT NULL,

    `cp` INT(5) NOT NULL,

    `ubicacion` TEXT NOT NULL,

    `latitud` FLOAT(15) NOT NULL,

    `longitud` FLOAT(15) NOT NULL,

    `id_sostenimiento` SMALLINT(3) NOT NULL,

    `sostenimiento` INT(7) NOT NULL,

    `id_subsostenimiento` TINYINT(2) NOT NULL,

    `subsostenimiento` VARCHAR(100) NOT NULL,

    `id_dependencia1` TINYINT(2) NOT NULL,

    `dependencia1` VARCHAR(100) NOT NULL,

    `id_dependencia2` TINYINT(2) NOT NULL,

    `dependencia2` VARCHAR(100) NOT NULL,

    `id_dependencia3` TINYINT(2) NOT NULL,

    `dependencia3` VARCHAR(80) NOT NULL,

    `id_dependencia4` TINYINT(2) NOT NULL,

    `dependencia4` VARCHAR(90) NOT NULL,

    `id_dependencia5` TINYINT(1) NOT NULL,

    `dependencia5` VARCHAR(60) NOT NULL,

    `id_servicio` VARCHAR(5) NOT NULL,

    `servicio` VARCHAR(115) NOT NULL,

    `id_opera_dependencia1` TINYINT(1) NOT NULL,

    `opera_dependencia1` VARCHAR(35) NOT NULL,

    `id_opera_dependencia2` TINYINT(1) NOT NULL,

    `opera_dependencia2` VARCHAR(95) NOT NULL,

    `id_opera_dependencia3` TINYINT(2) NOT NULL,

    `opera_dependencia3` VARCHAR(90) NOT NULL,

    `id_opera_dependencia4` TINYINT(2) NOT NULL,

    `opera_dependencia4` VARCHAR(90) NOT NULL,

    `id_opera_dependencia5` TINYINT(1) NOT NULL,

    `opera_dependencia5` VARCHAR(75) NOT NULL,

    `supervision` VARCHAR(10) NOT NULL,

    `curp` VARCHAR(18) NOT NULL,

    `rfc` VARCHAR(13) NOT NULL,

    `institucion` VARCHAR(10) NOT NULL,

    `turno1` TINYINT(20) NOT NULL,

    `turno2` TINYINT(20) NOT NULL,

    `turno3` TINYINT(20) NOT NULL,

    `id_nivel_servicio1` TINYINT(1) NOT NULL,

    `nivel_servicio1` VARCHAR(15) NOT NULL,

    `id_nivel_servicio2` TINYINT(1) NOT NULL,

    `nivel_servicio2`VARCHAR(25) NOT NULL,

    `id_nivel_servicio3` TINYINT(1) NOT NULL,

    `servicio_cam` TEXT NOT NULL,

    `id_caracteristica1` TINYINT(1) NOT NULL,

    `caracteristica1` VARCHAR(10) NOT NULL,

    `id_caracteristica2` TINYINT(2) NOT NULL,

    `caraceristica2` VARCHAR(10) NOT NULL,

    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id_plantel)

)
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

Así me quedaron las tablas de esta clase:

-- Creation of the lines table
CREATE TABLE `lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  `color` VARCHAR(15) NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`id`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Creation of the stations table
CREATE TABLE `stations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`id`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- Creation of the trains table
CREATE TABLE `trains` (
  `serial_number` VARCHAR(20) NOT NULL,
  `line_id` BIGINT UNSIGNED NOT NULL,
  `type` ENUM('moderno', 'viejo') NOT NULL,
  `year` YEAR NOT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY(`serial_number`)
)
DEFAULT CHARACTER SET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

MariaDB [metro_cdmx]> DESC `lines`;
+------------+---------------------+------+-----+---------------------+----------------+
| Field      | Type                | Null | Key | Default             | Extra          |
+------------+---------------------+------+-----+---------------------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| name       | varchar(10)         | NO   |     | NULL                |                |
| color      | varchar(15)         | NO   |     | NULL                |                |
| created_at | timestamp           | NO   |     | current_timestamp() |                |
| updated_at | timestamp           | NO   |     | current_timestamp() |                |
+------------+---------------------+------+-----+---------------------+----------------+
5 rows in set (0.001 sec)

MariaDB [metro_cdmx]> DESC `stations`;
+------------+---------------------+------+-----+---------------------+----------------+
| Field      | Type                | Null | Key | Default             | Extra          |
+------------+---------------------+------+-----+---------------------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| name       | varchar(50)         | NO   |     | NULL                |                |
| created_at | timestamp           | NO   |     | current_timestamp() |                |
| updated_at | timestamp           | NO   |     | current_timestamp() |                |
+------------+---------------------+------+-----+---------------------+----------------+
4 rows in set (0.001 sec)

MariaDB [metro_cdmx]> DESC `trains`;
+---------------+-------------------------+------+-----+---------------------+-------+
| Field         | Type                    | Null | Key | Default             | Extra |
+---------------+-------------------------+------+-----+---------------------+-------+
| serial_number | varchar(20)             | NO   | PRI | NULL                |       |
| line_id       | bigint(20) unsigned     | NO   |     | NULL                |       |
| type          | enum('moderno','viejo') | NO   |     | NULL                |       |
| year          | year(4)                 | NO   |     | NULL                |       |
| created_at    | timestamp               | NO   |     | current_timestamp() |       |
| updated_at    | timestamp               | NO   |     | current_timestamp() |       |
+---------------+-------------------------+------+-----+---------------------+-------+
6 rows in set (0.001 sec)

Para los que tienen la duda de por qué quedo así la tabla “stations”, precisamente en el siguiente vídeo se usara el comando “ALTER TABLE” para agregar los valores que le faltan al campo “id”.

Estos son:

USE metro_cdmx;

ALTER TABLE `stations`
MODIFY `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY(id);