Add FK in mysql table

Asked

Viewed 105 times

0

Good morning I am facing the following problem, I created 2 tables in the bank, while trying to create a fk in one of them generated the following error:#1215 - Cannot add Foreign key Constraint.

already checked and engine and is like Innodb, in the code tbm found no error, had already made the command in another table before so I’m a little lost on the cause, follows the codes of the tables: this first one worked and is with quotes pq I caught the DDL of her.

CREATE TABLE `tb_fatura` (
  `id_fatura` varchar(40) NOT NULL,
  `data_fatura` date DEFAULT NULL,
  `user` int(11) NOT NULL,
  `valor_total_fatura` double(4,2) DEFAULT NULL,
  `status_fatura` varchar(20) DEFAULT NULL,
  KEY `fk_id_user` (`user`),
  CONSTRAINT `fk_id_user` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

That second one didn’t work out.

create table tb_fatura_item(
id int(11) primary key not null auto_increment,
id_fatura varchar(40) not null,
id_produto int(11)  not null,
quantidade int(11) not null,
constraint fk_id_fatura foreign key(id_fatura)
references tb_fatura(id_fatura)
)engine = InnoDB ;

Table users

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(15) NOT NULL,
  `username` varchar(250) NOT NULL,
  `password` varchar(255) NOT NULL,
  `salt` varchar(255) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `activation_code` varchar(40) DEFAULT NULL,
  `forgotten_password_code` varchar(40) DEFAULT NULL,
  `forgotten_password_time` int(11) DEFAULT NULL,
  `remember_code` varchar(40) DEFAULT NULL,
  `created_on` int(11) NOT NULL,
  `last_login` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `phone` varchar(15) NOT NULL,
  `telefone_celular` varchar(15) DEFAULT NULL,
  `cpf_cnpj` varchar(18) DEFAULT NULL,
  `sexo` char(1) DEFAULT NULL,
  `data_nascimento` date DEFAULT NULL,
  `tipo_cliente` char(2) NOT NULL,
  `informacao_tributaria` char(1) DEFAULT NULL,
  `inscricao_estadual` varchar(12) DEFAULT NULL,
  `id_user_endereco` int(11) DEFAULT NULL,
  `identify_moodle` int(30) NOT NULL,
  `admin` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_LOGIN` (`email`,`password`),
  KEY `id_user_endereco` (`id_user_endereco`)
) ENGINE=InnoDB AUTO_INCREMENT=5460 DEFAULT CHARSET=latin1
mysql sql database

  • As is the table users?

  • I’ll add on question.

  • Note the creation order a table that FK should be created after the table you reference. users tb_invoice tb_invoice item

  • is exactly in that order, the first was the users (that already existed in a project), then I created tb_invoice and created in the end the tb_invoice

2 answers

2


The problem can be solved by changing the table :

'tb_invoice'

The field you are using from the table tb_invoice(id_invoice) to link in the table tb_invoice item is not primary key(PK).

This will work:

CREATE TABLE `tb_fatura` (
  `id_fatura` varchar(40) primary key NOT NULL,
  `data_fatura` date DEFAULT NULL,
  `user` int(11) NOT NULL,
  `valor_total_fatura` double(4,2) DEFAULT NULL,
  `status_fatura` varchar(20) DEFAULT NULL,
  KEY `fk_id_user` (`user`),
  CONSTRAINT `fk_id_user` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

0

Just for the record you can create a variable fk_key int and create the link afterwards (I find it easier)

alter table tb1
add constraint fk_chave
foreign key (fk_chave)
references tb2 (pk_id)

hug

Browser other questions tagged mysql sql database

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