SistemaComentariosWebApp/Codificación/BD_ETS.sql

DROP DATABASE IF EXISTS `ETS`;
CREATE DATABASE IF NOT EXISTS `ETS` 
DEFAULT CHARSET=utf8mb4;

USE `ETS`;

DROP TABLE IF EXISTS `EchedeyLR_Usuarios`;
DROP TABLE IF EXISTS `EchedeyLR_Comentarios`;

CREATE TABLE IF NOT EXISTS `EchedeyLR_Usuarios` (
	`Correo` VARCHARACTER(100) 
	NOT NULL,
	`Nombre` VARCHARACTER(20) 
	NOT NULL,
	`Contraseña` VARCHARACTER(128) 
	NOT NULL,
	PRIMARY KEY (`Correo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `EchedeyLR_Comentarios` (
	`Correo` VARCHARACTER(100) 
	NOT NULL,
	`Publicación` VARCHARACTER(255) 
	NOT NULL,
	`Tiempo` DATETIME 
	NOT NULL 
	DEFAULT NOW(),
	`Contenido` TEXT
	NOT NULL,
	PRIMARY KEY (`Correo`, `Publicación`, `Tiempo`),
	FOREIGN KEY(`Correo`) 
	REFERENCES `EchedeyLR_Usuarios`(`Correo`)
	ON UPDATE RESTRICT
	ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `EchedeyLR_Usuarios`
VALUES ("Guest", "Guest", "");

DROP TRIGGER IF EXISTS `AFTER_EchedeyLR_Usuarios_DELETE`;
DELIMITER $$
$$
CREATE DEFINER=`echedey`@`localhost` TRIGGER IF NOT EXISTS `AFTER_EchedeyLR_Usuarios_DELETE`
AFTER DELETE
ON `EchedeyLR_Usuarios`
FOR EACH ROW
BEGIN
	UPDATE `EchedeyLR_Comentarios`
	SET `Correo` = "Guest"
	WHERE `Correo` = OLD.`Correo`;
END$$
DELIMITER ;