Regrets

-- Ejercicios creación de tablas mysql - Juan José Martínez Solano - 29/11/2019

-- Creación de esquema
CREATE SCHEMA IF NOT EXISTS noviembre DEFAULT CHARACTER SET utf8 ;
CREATE USER [email protected] IDENTIFIED BY 'PepeGrillo';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, REFERENCES, CREATE TEMPORARY TABLES ON noviembre.* TO 'LazyReflexes'@'172.18.0.1';
USE noviembre;

-- Valores únicos 1
CREATE TABLE IF NOT EXISTS tabla (
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(55) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX nombre_UNIQUE (`nombre` ASC))
  ENGINE = InnoDB;

INSERT INTO tabla(nombre) values ('test');
INSERT INTO tabla(nombre) values ('test'); -- Error, no se permite introducir el mismo valor dos veces en la tabla
INSERT INTO tabla(nombre) values (NULL);
INSERT INTO tabla(nombre) values (NULL); -- Se permite introducir el valor NULL indefinidamente

select * from tabla;

-- Valores únicos 2
CREATE TABLE IF NOT EXISTS tabla2 (
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(55) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX nombre_UNIQUE (`nombre` ASC))
  ENGINE = InnoDB;

INSERT INTO tabla2(nombre) values ('test2');
INSERT INTO tabla2(nombre) values ('test2'); -- Error, no permite duplicados
INSERT INTO tabla2(nombre) values (NULL); -- Error, no se permite NULL

select * from tabla2;

-- Valores únicos 3

/*
CREATE TABLE IF NOT EXISTS puestos (
  `id` INT NOT NULL AUTO_INCREMENT,
  `puesto` VARCHAR(45) NULL,
  `nombre` VARCHAR(45) NULL,
  `trabajador` varchar(128) AS (IF(puesto<nombre,CONCAT(puesto,nombre),CONCAT(nombre,puesto))),
  PRIMARY KEY (`id`),
  UNIQUE KEY trabajador(trabajador))
ENGINE = InnoDB;
*/

CREATE TABLE IF NOT EXISTS puestos (
  `id` INT NOT NULL AUTO_INCREMENT,
  `puesto` VARCHAR(45) NULL,
  `nombre` VARCHAR(45) NULL,
  UNIQUE (puesto, nombre),
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;

insert into puestos (puesto, nombre) values ('oficinista', 'Pepe');
insert into puestos (puesto, nombre) values ('oficinista', 'Pepe'); -- No admite una entrada duplicada.

insert into puestos (puesto) values (NULL);
insert into puestos (puesto) values (NULL); -- Admite el valor NULL indefinidamente
insert into puestos (nombre) values (NULL);
insert into puestos (nombre) values (NULL); -- Admite el valor NULL indefinidamente

insert into puestos (puesto, nombre) values (NULL, NULL);
insert into puestos (puesto, nombre) values (NULL, NULL); -- Admite el valor NULL en ambas columnas indefinidamente

select * from puestos;

-- Valor por defecto
CREATE TABLE IF NOT EXISTS persona (
  `f_nacimiento` DATE NULL DEFAULT '2199-12-31',
  `id` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

insert into persona(nombre) values ('Persona Test 1');
insert into persona(nombre, f_nacimiento) values ('Persona Test 2', NULL); -- Ajusta el valor de f_nacimiento a NULL

select * from persona;

-- checks 1

CREATE TABLE IF NOT EXISTS `nota` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(45) NOT NULL,
  `nota` INT NOT NULL CHECK (nota >= 0 and nota <= 10),
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;

insert into nota (nombre, nota) values ('Pepe', 7);
insert into nota (nombre, nota) values ('Pepe', 11); -- Error, el valor de nota supera el límite establecido por CHECK

desc nota;

show create table nota;

show tables; -- sólo sirve después de usar el use

show databases;

select * from coche;

select * from persona;

show create table coche;

update persona set nombre = 'Juan' where id = 2;

update persona set nombre = 'Antonio' where id = 3;

insert into coche (marca, modelo, persona_id)
values ('seat', 'ibiza', 1);

insert into coche (marca, modelo, persona_id)
values ('renault', 'megane', 1);

delete from persona where id = 1; -- error por restricción
update persona set id=7 where id = 1; -- error por restricción

CREATE TABLE IF NOT EXISTS `noviembre`.`coche` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `marca` VARCHAR(45) NULL,
  `modelo` VARCHAR(45) NULL,
  `persona_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_coche_persona_idx` (`persona_id` ASC),
    FOREIGN KEY (`persona_id`)
    REFERENCES `noviembre`.`persona` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

alter table coche drop foreign key `fk_coche_persona`; -- quitamos la restricción (se pone entre comillas el constraint)

alter table coche -- modificamos la acción al borrar, eliminando la restricción y haciendo "cascada"
    add CONSTRAINT `fk_coche_persona`
    FOREIGN KEY (`persona_id`)
    REFERENCES `noviembre`.`persona` (`id`)
    ON DELETE cascade
    ON UPDATE cascade; -- si eliminamos una persona, los coches de essa persona se eliminan

select * from coche;

delete from persona where id=1;

select * from persona;