Duplicate key in mysql

Asked

Viewed 1,170 times

2

I am in the third quarter of database and we started using the Internet in our database but I came across the error

"Error Code: 1022. Can’t write; Duplicate key in table '#sql-aa4_a".

Is there a function that can solve this problem or is my code incorrect ? follows it below.

-- drop database `chapelao`; -- caso eu não use o alter table  devo dropar a tabela
CREATE DATABASE IF NOT EXISTS `chapelao`;

use `chapelao`;

create table if not exists empresa(
    idempresa int auto_increment not null,
    cnpj varchar (45) not null,
    primary key (idempresa)
)Engine = InnoDB;


create table if not exists clientes(
    idclientes int auto_increment not null,
    rg varchar (45) not null,
    cpf varchar (45) not null,
    datadenascimento date not null,
    empresaid int not null,
    primary key (idclientes)
)Engine = InnoDB;

create table if not exists colaboradoras (
    idcolaboradora int auto_increment not null,
    nome varchar (45) not null,
    idade int not null,
    balao bool not null,
    rg varchar (45) not null,
    cpf varchar (45) not null,
    empresaid int not null,
    primary key (idcolaboradora)
)Engine = InnoDB;

alter table colaboradoras 
    add constraint fk_empresa_colaboradoras
        foreign key (empresaid)
        references empresa (idempresa)
        on delete no action
        on update no action;

alter table clientes 
    add constraint fk_empresa_clientes
        foreign key (empresaid)
        references empresa (idempresa)
        on delete no action
        on update no action;

insert into empresa (idempresa ,cnpj) values (null ,"08812216904");
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Taiana","19",1,"15011","15022",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Debora","25",0,"15012","15023",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Veronica","30",0,"15013","15024",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Aline","22",0,"15014","150245",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Luciana","23",1,"15015","15026",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Carol","24",0,"15016","15027",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Thalya","18",1,"15017","15028",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Julinha","20",0,"15018","15029",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Maria","25",1,"15019","150210",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Polyanna","35",1,"150110","150212",1);
insert into colaboradoras (idcolaboradora, nome, idade, balao, rg, cpf, empresaid) values (null,"Giovana","20",0,"150111","150213",1);
mysql

  • I ran your script and here the error did not occur; I suggest you : Drop the database and run the script again, instruction by instruction; I hope to have helped;

  • @Bacco your solution was effective thank you

  • @Lucianosilva even dropping the database the error was still on line 6 but I believe I solved the problem with the tip of Bacco

1 answer

1

In a brief research I detected that, the functioning of INSERT Mysql is similar to Postgresql: when there is a ID specified, the database does not update the entered record number, therefore, by entering records informing the PK you should also inform the ID which is being inserted. In case you don’t want to worry about ID remove the same from INSERT.

Would look like this:

insert into colaboradoras (nome, idade, balao, rg, cpf, empresaid) values ("Taiana","19",1,"15011","15022",1);

Source: https://www.w3schools.com/sql/sql_insert.asp

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.