Foreign key creation error Mariadb

Asked

Viewed 184 times

0

Hello, I am creating the following tables in my Mariadb database:

 CREATE TABLE `compras`
    (
      `id` int PRIMARY KEY,
      `aberto_por` int,
      `titulo` varchar(255) NOT NULL,
      `empresa` int NOT NULL,
      `orcamento` int NOT NULL,
      `forma_pagamento` int NOT NULL COMMENT '1 - vista / 2 - parcelado',
      `parcelas` int NOT NULL DEFAULT 1,
      `valor_parcelas` double,
      `sub_total` double NOT NULL,
      `desconto` double,
      `total` double NOT NULL,
      `finalidade` text NOT NULL,
      `uso` text NOT NULL,
      `local` text NOT NULL,
      `fornecedores` text,
      `id_sap` int,
      `criado_em` datetime NOT NULL,
      `duvidas` varchar(255)
    );
    
    CREATE TABLE `compras_status`
    (
      `id` int PRIMARY KEY,
      `id_situacao` int NOT NULL,
      `id_compra` int NOT NULL,
      `id_usuario` int NOT NULL,
      `informacoes` varchar(255),
      `data` datetime NOT NULL
    );
    
    CREATE TABLE `compras_situacao`
    (
      `id` int PRIMARY KEY,
      `descricao` varchar(255) NOT NULL
    );
    
    CREATE TABLE `compras_anexos`
    (
      `id` int PRIMARY KEY,
      `id_compra` int,
      `nome_arquivo` varchar(255) NOT NULL,
      `tipo_anexo` int
    );
    
    CREATE TABLE `compras_avaliadores`
    (
      `id` int PRIMARY KEY,
      `id_avaliador` int,
      `id_compra` int,
      `id_centrocusto` int,
      `aprovado` int DEFAULT 0
    );
    
    CREATE TABLE `membros`
    (
      `id` int PRIMARY KEY
    );
    
    CREATE TABLE `compras_itens`
    (
      `id` int PRIMARY KEY,
      `id_compra` int,
      `descricao` varchar(255) NOT NULL,
      `quantidade` varchar(255) NOT NULL,
      `valor_unitario` double NOT NULL,
      `total_item` double NOT NULL
    );
    
    ALTER TABLE `compras` ADD FOREIGN KEY (`aberto_por`) REFERENCES `membros` (`id`);
    
    ALTER TABLE `compras_status` ADD FOREIGN KEY (`id_situacao`) REFERENCES `compras_situacao` (`id`);
    
    ALTER TABLE `compras_status` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);
    
    ALTER TABLE `compras_status` ADD FOREIGN KEY (`id_usuario`) REFERENCES `membros` (`id`);
    
    ALTER TABLE `compras_anexos` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);
    
    ALTER TABLE `compras_avaliadores` ADD FOREIGN KEY (`id_avaliador`) REFERENCES `membros` (`id`);
    
    ALTER TABLE `compras_avaliadores` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);
    
    ALTER TABLE `compras_itens` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);

Remembering that the members table is already created. When I have it executed, it shows the following error:

1005 - You cannot create the table sistemapib.compras_anexos (error no. 150 "Foreign key Constraint is incorrectly Formed")

What could it be? Thanks in advance.

mysql script mariadb

  • I think compas_attachments.id_purchase should be not null

  • This error may appear in several errors, use the command SHOW ENGINE INNODB STATUS to have more details of the problem.

  • Thanks for the answers, but none of them solved, the problem persists.

1 answer

0

As good practice, it is always important to mark as UNSIGNED the ID columns, avoiding problems related to negative numbers.

Try the following creation query:

CREATE TABLE `compras`
(
  `id` int UNSIGNED PRIMARY KEY,
  `aberto_por` int,
  `titulo` varchar(255) NOT NULL,
  `empresa` int NOT NULL,
  `orcamento` int NOT NULL,
  `forma_pagamento` int NOT NULL COMMENT '1 - vista / 2 - parcelado',
  `parcelas` int NOT NULL DEFAULT 1,
  `valor_parcelas` double,
  `sub_total` double NOT NULL,
  `desconto` double,
  `total` double NOT NULL,
  `finalidade` text NOT NULL,
  `uso` text NOT NULL,
  `local` text NOT NULL,
  `fornecedores` text,
  `id_sap` int,
  `criado_em` datetime NOT NULL,
  `duvidas` varchar(255)
) ENGINE=InnoDB;

CREATE TABLE `compras_status`
(
  `id` int UNSIGNED PRIMARY KEY,
  `id_situacao` int NOT NULL,
  `id_compra` int NOT NULL,
  `id_usuario` int NOT NULL,
  `informacoes` varchar(255),
  `data` datetime NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `compras_situacao`
(
  `id` int PRIMARY KEY,
  `descricao` varchar(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `compras_anexos`
(
  `id` int UNSIGNED PRIMARY KEY,
  `id_compra` int,
  `nome_arquivo` varchar(255) NOT NULL,
  `tipo_anexo` int
) ENGINE=InnoDB;

CREATE TABLE `compras_avaliadores`
(
  `id` int UNSIGNED PRIMARY KEY,
  `id_avaliador` int,
  `id_compra` int,
  `id_centrocusto` int,
  `aprovado` int DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE `membros`
(
  `id` int UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE `compras_itens`
(
  `id` int UNSIGNED PRIMARY KEY,
  `id_compra` int,
  `descricao` varchar(255) NOT NULL,
  `quantidade` varchar(255) NOT NULL,
  `valor_unitario` double NOT NULL,
  `total_item` double NOT NULL
) ENGINE=InnoDB;

ALTER TABLE `compras` ADD FOREIGN KEY (`aberto_por`) REFERENCES `membros` (`id`);

ALTER TABLE `compras_status` ADD FOREIGN KEY (`id_situacao`) REFERENCES `compras_situacao` (`id`);

ALTER TABLE `compras_status` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);

ALTER TABLE `compras_status` ADD FOREIGN KEY (`id_usuario`) REFERENCES `membros` (`id`);

ALTER TABLE `compras_anexos` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);

ALTER TABLE `compras_avaliadores` ADD FOREIGN KEY (`id_avaliador`) REFERENCES `membros` (`id`);

ALTER TABLE `compras_avaliadores` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);

ALTER TABLE `compras_itens` ADD FOREIGN KEY (`id_compra`) REFERENCES `compras` (`id`);

Browser other questions tagged mysql script mariadb

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