Problem with BD (foreign key)

Question

My script is as follows. It is giving foreign key error and I cannot solve.

CREATE DATABASE teste;
USE teste;

CREATE TABLE horario (
id_horario int PRIMARY KEY NOT NULL AUTO_INCREMENT,
horario time NOT NULL
);

CREATE TABLE linha (
id_linha int PRIMARY KEY NOT NULL AUTO_INCREMENT,
nome varchar(255) NOT NULL,
codigo varchar(6) NOT NULL,    
empresa varchar(50) NOT NULL,
sentido boolean NOT NULL
);

CREATE TABLE `markers` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `address` varchar(80) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `type` varchar(30) NOT NULL,
  `cidade` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `markers` (`id`, `name`, `address`, `lat`, `lng`, `type`,             
`cidade`) VALUES
(1, 'Bushere', '1344 R. Jose Demetrio Coelho - Centro, Carmo do Cajuru',     
-20.189388, -44.767197, 'Ponto', 'Carmo do Cajuru'),
(2, 'Ponto Borracharia do Macarrão', '21 R. São Paulo Centro, Carmo do 
Cajuru', -20.189863, -44.767570, 'Ponto', 'Carmo do Cajuru'),
(5, 'Ponto do Bairro Viória', 'Carmo do Cajuru - MG, 35557-000, B', 
-20.195044, -44.749783, 'Ponto', 'Carmo do Cajuru');

CREATE TABLE localizacao (
id_localizacao int PRIMARY KEY NOT NULL AUTO_INCREMENT,
id_onibus int NOT NULL,
id_ponto int NOT NULL,
horario time NOT NULL,
status int NOT NULL,
FOREIGN KEY(id_ponto) REFERENCES markers (id)
);

CREATE TABLE `usuario` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL,
  `telefone` varchar(12) NOT NULL,
  `endereco` varchar(255) NOT NULL,
  `cidade` varchar(100) NOT NULL,
  `data_nasc` date NOT NULL,
  `user` varchar(50) NOT NULL,
  `senha` varchar(50) NOT NULL,
  `pontos` int(11) NOT NULL,
  `tipo` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;    

INSERT INTO `usuario` (`id`, `nome`, `telefone`, `endereco`, `cidade`, `data_nasc`, `user`, `senha`, `pontos`, `tipo`) VALUES
(1, 'Gabriel Rabelo Camargos', '37 998637969', 'Azaleias 106 - Vitória', 'Carmo do Cajuru', '2000-07-04', 'omitogabriel', '123', 0, 1),
(2, 'Usuário', '37 998637969', 'Rua 2 n 371', 'Carmo do Cajuru', '2000-07-04', 'user', '123', 10, 0),
(3, 'Administrador', '99999999', 'Rua 2 nº 371', 'Carmo do     Cajuru', '2000-07-04', 'adm', '123', 0, 0),
(4, 'Eduardo Sousa', '(37) 98854-9', 'Rua Pedro Martins Machado, 411', 'Divinópolis', '2000-01-15', 'dudu', '123', 0, 0);


CREATE TABLE rota (
id_rota int PRIMARY KEY NOT NULL AUTO_INCREMENT,
id_ponto int NOT NULL,
id_linha int NOT NULL,
dia_semana varchar(6),
sentido varchar(255),
FOREIGN KEY(id_ponto) REFERENCES markers (id),
FOREIGN KEY(id_linha) REFERENCES linha (id_linha)
);

CREATE TABLE onibus (
id_onibus int PRIMARY KEY NOT NULL AUTO_INCREMENT,
id_horario int NOT NULL,
id_linha int NOT NULL,
numero varchar(6),
FOREIGN KEY(id_horario) REFERENCES horario (id_horario),
FOREIGN KEY(id_linha) REFERENCES linha (id_linha)
);

ALTER TABLE localizacao ADD FOREIGN KEY(id_onibus) REFERENCES onibus (id_onibus)

Answer 1

There are some silly little problems in your script. The cause of your problem is that the table markers is as MyISAM. MyISAM has no foreign key support, and do not recommend using MyISAM for absolutely nothing. Use InnoDB instead.

However, there are some other little problems too:

• Breaking lines inside a string is a bad idea:

  (2, 'Ponto Borracharia do Macarrão', '21 R. São Paulo Centro, Carmo do 
  Cajuru', -20.189863, -44.767570, 'Ponto', 'Carmo do Cajuru'),
  

• There are encoding/charset problems: Macarrão, São Paulo, Viória, Vitória, Usuário, nº and Divinópolis.

• To avoid encoding/charset problems always use utf8. Do not use latin1.

Therefore, I recommend putting ENGINE=InnoDB DEFAULT CHARSET=utf8; at the end of all their CREATE TABLEs.

Your corrected code looks like this:

CREATE DATABASE teste;
USE teste;

CREATE TABLE horario (
  id_horario int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  horario time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE linha (
  id_linha int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  nome varchar(255) NOT NULL,
  codigo varchar(6) NOT NULL,    
  empresa varchar(50) NOT NULL,
  sentido boolean NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `markers` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `address` varchar(80) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  `type` varchar(30) NOT NULL,
  `cidade` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `markers` (`id`, `name`, `address`, `lat`, `lng`, `type`, `cidade`) VALUES
(1, 'Bushere', '1344 R. Jose Demetrio Coelho - Centro, Carmo do Cajuru', -20.189388, -44.767197, 'Ponto', 'Carmo do Cajuru'),
(2, 'Ponto Borracharia do Macarrão', '21 R. São Paulo Centro, Carmo do Cajuru', -20.189863, -44.767570, 'Ponto', 'Carmo do Cajuru'),
(5, 'Ponto do Bairro Viária', 'Carmo do Cajuru - MG, 35557-000, B', -20.195044, -44.749783, 'Ponto', 'Carmo do Cajuru');

CREATE TABLE localizacao (
  id_localizacao int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  id_onibus int NOT NULL,
  id_ponto int NOT NULL,
  horario time NOT NULL,
  status int NOT NULL,
  FOREIGN KEY(id_ponto) REFERENCES markers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `usuario` (
  `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) NOT NULL,
  `telefone` varchar(12) NOT NULL,
  `endereco` varchar(255) NOT NULL,
  `cidade` varchar(100) NOT NULL,
  `data_nasc` date NOT NULL,
  `user` varchar(50) NOT NULL,
  `senha` varchar(50) NOT NULL,
  `pontos` int(11) NOT NULL,
  `tipo` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `usuario` (`id`, `nome`, `telefone`, `endereco`, `cidade`, `data_nasc`, `user`, `senha`, `pontos`, `tipo`) VALUES
(1, 'Gabriel Rabelo Camargos', '37 998637969', 'Azaleias 106 - Vitória', 'Carmo do Cajuru', '2000-07-04', 'omitogabriel', '123', 0, 1),
(2, 'Usuário', '37 998637969', 'Rua 2 n 371', 'Carmo do Cajuru', '2000-07-04', 'user', '123', 10, 0),
(3, 'Administrador', '99999999', 'Rua 2 nº 371', 'Carmo do Cajuru', '2000-07-04', 'adm', '123', 0, 0),
(4, 'Eduardo Sousa', '(37) 98854-9', 'Rua Pedro Martins Machado, 411', 'Divinópolis', '2000-01-15', 'dudu', '123', 0, 0);

CREATE TABLE rota (
  id_rota int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  id_ponto int NOT NULL,
  id_linha int NOT NULL,
  dia_semana varchar(6),
  sentido varchar(255),
  FOREIGN KEY(id_ponto) REFERENCES markers (id),
  FOREIGN KEY(id_linha) REFERENCES linha (id_linha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE onibus (
  id_onibus int PRIMARY KEY NOT NULL AUTO_INCREMENT,
  id_horario int NOT NULL,
  id_linha int NOT NULL,
  numero varchar(6),
  FOREIGN KEY(id_horario) REFERENCES horario (id_horario),
  FOREIGN KEY(id_linha) REFERENCES linha (id_linha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE localizacao ADD FOREIGN KEY(id_onibus) REFERENCES onibus (id_onibus);

See here working on sqlfiddle.