Error Code: 1048, SQL State: 23000 Column 'colegiado_id' cannot be null

Question

Hello, this mistake

"[Error Code: 1048, SQL State: 23000]  Column 'colegiado_id' cannot be null"

happens when I try to run the SQL command below directly in Mysql:

start transaction;
INSERT INTO EspecialistasColegiados (colegiado_id, especialista_id) (
    select
        col.id as colegiado_id,
        esp.id as especialista_id

    from Especialista esp
        inner join importacao imp ON imp.idimportacao = esp.id
        left join Colegiado col ON col.nome like concat('%',imp.CURSOS,'%')
    );
rollback;

Answer 1

The "colegiado_id" column cannot be null. You have two alternatives:

perform an alter table in the Special table chosen to accept null value or change the query (select) not to return null values

Alter in the table

ALTER TABLE `MyTable` ALTER COLUMN `MinhaColuna` INT DEFAULT NULL;

Modify select

 start transaction;
 INSERT INTO EspecialistasColegiados (colegiado_id, especialista_id) (
select
    col.id as colegiado_id,
    esp.id as especialista_id

from Especialista esp
    inner join importacao imp ON imp.idimportacao = esp.id
    left join Colegiado col ON col.nome like concat('%',imp.CURSOS,'%')
where colegiado_id IS NOT NULL
 );
rollback;