I can not put a data with null value in a null table mysql workbench

Navigation
1

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, only the rows that have all the values, and the rows that They have empty fields, they do not load them. It is assumed that if you do not have the option of not null, you can take null values. This is the code of my database, the problem I have with the table box supplies: 

-- 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_0900_ai_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;
    
asked by Diego Chavez 20.11.2018 в 20:48
source

1 answer

1
  

I'll explain it to you through the following example so you can see how   manipulate both columns NULL as NOT NULL

We have the following structure of a table 

CREATE TABLE demo(
  name VARCHAR(20) NOT NULL,
  email VARCHAR(40) NULL,
  age INT NULL DEFAULT 0
);

OBSERVATIONS

  • The field name is NOT NULL therefore it will always require a value to be entered
  • The field email is NULL therefore it may be empty or you can assign a default value
  • The field age is NULL but so that it is not empty, assign it a default value, which means that if a user does not enter anything then the column takes the value of 0

    • EXAMPLE 1

    I will not insert any value for email and for age then the structure of my query should be the following 

    INSERT INTO demo(name)
VALUES
("Alfredo");

    And if I do a SELECT you should get this 

    SELECT * FROM demo;

name     email  age
Alfredo         0

    EXPLANATION

    As you do not assign any value to the last two columns, then do not invoke them at the time of doing the INSERT

    EXAMPLE 2 

    INSERT INTO demo(name, email)
VALUES
("Alfredo", "[email protected]");

    EXPLANATION

    This time if I invoke 2 columns in the INSERT because I will give a value to email but as a age I will leave the default value then I do not place it in the sentence

    My result if I do a SELECT should be 

    SELECT * FROM demo;

name    email          age
Alfredo [email protected]   0

    EXAMPLE 3

    This time I will assign a custom value to each column of the 3 existing ones, therefore if I place them in the statement of INSERT 

    INSERT INTO demo(name, email, age)
VALUES
("Alfredo", "[email protected]", 12);

    If I do a SELECT my result should look like this 

    SELECT * FROM demo;

name    email           age
Alfredo [email protected]   12
        
    answered by 20.11.2018 / 22:21
    source
    Strings with single or double quotes [closed] WebView through setContentView