I have a problem, I have a table with some fields not null, and a couple of fields that can have null values, the problem is when I want to upload data to it, I only load the rows that have all the values, and the rows that have empty fields it doesn't load them. It is assumed that if you don't have the not null option, you can take null values. This is the code of my database, the problem I have is with the input box table:
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema pizzahutalmacenbd
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `pizzahutalmacenbd` ;
-- -----------------------------------------------------
-- Schema pizzahutalmacenbd
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `pizzahutalmacenbd` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;
USE `pizzahutalmacenbd` ;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`guiaremision`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`guiaremision` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`guiaremision` (
`idGuiaRemision` INT(11) NOT NULL,
`FechaEntrega` DATE NOT NULL,
PRIMARY KEY (`idGuiaRemision`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`tipoinsumo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`tipoinsumo` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`tipoinsumo` (
`idTipoInsumo` INT(11) NOT NULL,
`Tipo` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idTipoInsumo`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`unidad`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`unidad` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`unidad` (
`idUnidad` INT(11) NOT NULL,
`Unidad` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idUnidad`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`insumos`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`insumos` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`insumos` (
`idInsumos` INT(11) NOT NULL,
`Nombre` VARCHAR(45) NOT NULL,
`Cantidad` FLOAT NOT NULL,
`VidaUtil` INT NULL,
`Descongelamiento` INT(100) NULL,
`TipoInsumo_idTipoInsumo` INT(11) NOT NULL,
`Unidad_idUnidad` INT(11) NOT NULL,
`precioUnitario` DOUBLE NOT NULL,
PRIMARY KEY (`idInsumos`),
INDEX `fk_Insumos_TipoInsumo_idx` (`TipoInsumo_idTipoInsumo` ASC) VISIBLE,
INDEX `fk_Insumos_Unidad1_idx` (`Unidad_idUnidad` ASC) VISIBLE,
CONSTRAINT `fk_Insumos_TipoInsumo`
FOREIGN KEY (`TipoInsumo_idTipoInsumo`)
REFERENCES `pizzahutalmacenbd`.`tipoinsumo` (`idTipoInsumo`),
CONSTRAINT `fk_Insumos_Unidad1`
FOREIGN KEY (`Unidad_idUnidad`)
REFERENCES `pizzahutalmacenbd`.`unidad` (`idUnidad`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordenbaja`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordenbaja` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordenbaja` (
`idOrdenBaja` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
PRIMARY KEY (`idOrdenBaja`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`responsablecocina`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`responsablecocina` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`responsablecocina` (
`idResponsableCocina` INT(11) NOT NULL,
`Nombre` VARCHAR(45) NOT NULL,
`Apellidos` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idResponsableCocina`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordendescongelado`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordendescongelado` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordendescongelado` (
`idOrdenDescongelado` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
`ResponsableCocina_idResponsableCocina` INT(11) NOT NULL,
PRIMARY KEY (`idOrdenDescongelado`),
INDEX `fk_OrdenDescongelado_ResponsableCocina1_idx` (`ResponsableCocina_idResponsableCocina` ASC) VISIBLE,
CONSTRAINT `fk_OrdenDescongelado_ResponsableCocina1`
FOREIGN KEY (`ResponsableCocina_idResponsableCocina`)
REFERENCES `pizzahutalmacenbd`.`responsablecocina` (`idResponsableCocina`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordenpedido`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordenpedido` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordenpedido` (
`idOrdenPedido` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
PRIMARY KEY (`idOrdenPedido`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ubicacion`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ubicacion` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ubicacion` (
`idUbicacion` INT(11) NOT NULL,
`Lugar` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idUbicacion`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`cajainsumo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`cajainsumo` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`cajainsumo` (
`idCajaInsumo` INT(11) NOT NULL,
`FechaVencimiento` DATE NOT NULL,
`Stock` FLOAT NOT NULL,
`Insumos_idInsumos` INT(11) NOT NULL,
`OrdenBaja_idOrdenBaja` INT(11) NULL,
`GuiaRemision_idGuiaRemision` INT(11) NOT NULL,
`OrdenPedido_idOrdenPedido` INT(11) NOT NULL,
`OrdenDescongelado_idOrdenDescongelado` INT(11) NULL,
`Ubicación_idUbicación` INT(11) NOT NULL,
`CantidadBaja` FLOAT NULL,
PRIMARY KEY (`idCajaInsumo`),
INDEX `fk_CajaInsumo_Insumos1_idx` (`Insumos_idInsumos` ASC) VISIBLE,
INDEX `fk_CajaInsumo_OrdenBaja1_idx` (`OrdenBaja_idOrdenBaja` ASC) VISIBLE,
INDEX `fk_CajaInsumo_GuiaRemision1_idx` (`GuiaRemision_idGuiaRemision` ASC) VISIBLE,
INDEX `fk_CajaInsumo_OrdenPedido1_idx` (`OrdenPedido_idOrdenPedido` ASC) VISIBLE,
INDEX `fk_CajaInsumo_OrdenDescongelado1_idx` (`OrdenDescongelado_idOrdenDescongelado` ASC) VISIBLE,
INDEX `fk_CajaInsumo_Ubicación1_idx` (`Ubicación_idUbicación` ASC) VISIBLE,
CONSTRAINT `fk_CajaInsumo_GuiaRemision1`
FOREIGN KEY (`GuiaRemision_idGuiaRemision`)
REFERENCES `pizzahutalmacenbd`.`guiaremision` (`idGuiaRemision`),
CONSTRAINT `fk_CajaInsumo_Insumos1`
FOREIGN KEY (`Insumos_idInsumos`)
REFERENCES `pizzahutalmacenbd`.`insumos` (`idInsumos`),
CONSTRAINT `fk_CajaInsumo_OrdenBaja1`
FOREIGN KEY (`OrdenBaja_idOrdenBaja`)
REFERENCES `pizzahutalmacenbd`.`ordenbaja` (`idOrdenBaja`),
CONSTRAINT `fk_CajaInsumo_OrdenDescongelado1`
FOREIGN KEY (`OrdenDescongelado_idOrdenDescongelado`)
REFERENCES `pizzahutalmacenbd`.`ordendescongelado` (`idOrdenDescongelado`),
CONSTRAINT `fk_CajaInsumo_OrdenPedido1`
FOREIGN KEY (`OrdenPedido_idOrdenPedido`)
REFERENCES `pizzahutalmacenbd`.`ordenpedido` (`idOrdenPedido`),
CONSTRAINT `fk_CajaInsumo_Ubicación1`
FOREIGN KEY (`Ubicación_idUbicación`)
REFERENCES `pizzahutalmacenbd`.`ubicacion` (`idUbicacion`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`ordensalida`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`ordensalida` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`ordensalida` (
`idOrdenSalida` INT(11) NOT NULL,
`Fecha` DATE NOT NULL,
`ResponsableCocina_idResponsableCocina` INT(11) NOT NULL,
`Turno` TINYINT(1) NOT NULL,
PRIMARY KEY (`idOrdenSalida`),
INDEX `fk_OrdenSalida_ResponsableCocina1_idx` (`ResponsableCocina_idResponsableCocina` ASC) VISIBLE,
CONSTRAINT `fk_OrdenSalida_ResponsableCocina1`
FOREIGN KEY (`ResponsableCocina_idResponsableCocina`)
REFERENCES `pizzahutalmacenbd`.`responsablecocina` (`idResponsableCocina`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`cajainsumo_has_ordensalida`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`cajainsumo_has_ordensalida` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`cajainsumo_has_ordensalida` (
`CajaInsumo_idCajaInsumo` INT(11) NOT NULL,
`OrdenSalida_idOrdenSalida` INT(11) NOT NULL,
`Cantidad` FLOAT NOT NULL,
PRIMARY KEY (`CajaInsumo_idCajaInsumo`, `OrdenSalida_idOrdenSalida`),
INDEX `fk_CajaInsumo_has_OrdenSalida_OrdenSalida1_idx` (`OrdenSalida_idOrdenSalida` ASC) VISIBLE,
INDEX `fk_CajaInsumo_has_OrdenSalida_CajaInsumo1_idx` (`CajaInsumo_idCajaInsumo` ASC) VISIBLE,
CONSTRAINT `fk_CajaInsumo_has_OrdenSalida_CajaInsumo1`
FOREIGN KEY (`CajaInsumo_idCajaInsumo`)
REFERENCES `pizzahutalmacenbd`.`cajainsumo` (`idCajaInsumo`),
CONSTRAINT `fk_CajaInsumo_has_OrdenSalida_OrdenSalida1`
FOREIGN KEY (`OrdenSalida_idOrdenSalida`)
REFERENCES `pizzahutalmacenbd`.`ordensalida` (`idOrdenSalida`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`dias`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`dias` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`dias` (
`idDias` INT(11) NOT NULL,
`Dia` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idDias`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `pizzahutalmacenbd`.`demanda`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `pizzahutalmacenbd`.`demanda` ;
CREATE TABLE IF NOT EXISTS `pizzahutalmacenbd`.`demanda` (
`Insumos_idInsumos` INT(11) NOT NULL,
`Dias_idDias` INT(11) NOT NULL,
`Demanda` FLOAT NOT NULL,
PRIMARY KEY (`Insumos_idInsumos`, `Dias_idDias`),
INDEX `fk_Insumos_has_Días_Días1_idx` (`Dias_idDias` ASC) VISIBLE,
INDEX `fk_Insumos_has_Días_Insumos1_idx` (`Insumos_idInsumos` ASC) VISIBLE,
CONSTRAINT `fk_Insumos_has_Días_Días1`
FOREIGN KEY (`Dias_idDias`)
REFERENCES `pizzahutalmacenbd`.`dias` (`idDias`),
CONSTRAINT `fk_Insumos_has_Días_Insumos1`
FOREIGN KEY (`Insumos_idInsumos`)
REFERENCES `pizzahutalmacenbd`.`insumos` (`idInsumos`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
We have the following structure of a table
OBSERVATIONS
name
isNOT NULL
therefore always going to require a value to be enteredemail
isNULL
therefore can be left empty or you can assign a default valueage
isNULL
but so that it is not empty, assign a default value, which means that if a user does not enter anything then the column takes the value of0
EXAMPLE 1
I am not going to insert any value for
email
and soage
the structure of my query should be the followingAnd if I make a I
SELECT
should get thisEXPLANATION
Since I don't assign any value to the last two columns, then I don't call them at the time of doing the
INSERT
EXAMPLE 2
EXPLANATION
This time if I invoke 2 columns in the
INSERT
why I will give a value toemail
but sinceage
I will leave the default value so I do not place it in the sentenceMy result if I do a
SELECT
should beEXAMPLE 3
This time I will assign a custom value to each column of the 3 existing ones, so if I place them in the statement of
INSERT
If I do a
SELECT
my result should look like this