CODEWITHSUNDEEP
mysqlsql
babboe2 babboe2
babboe2 babboe2

Reputation: 85

Cannot add Foreign Key MySQL :errno 150

I try add foreign key:

   CREATE TABLE `invoices` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
  ` name` varchar(255) NOT NULL,
   `code_text` text NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




  CREATE TABLE `leads` (
  `lead_id` int(11) NOT NULL AUTO_INCREMENT,
   ...
   PRIMARY KEY (`lead_id`)
  );



CREATE TABLE `leads_invoices` (
leads_invoice_id int(11) AUTO_INCREMENT NOT NULL,
   invoice_id int(11) NOT NULL,
   lead_id_i  int(11) NOT NULL,
   PRIMARY KEY(leads_invoice_id),
    FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    FOREIGN KEY (lead_id_i) REFERENCES leads(lead_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8

But I got error with my lead_id_i key:

Can't create table crm_test.leads_invoices (errno: 150 "Foreign key constraint is incorrectly formed")

I checked all types and tables, seems they are correct.. How can I solve this problem? Hope you will help me. Thanks!

Upvotes: 0

Views: 47

Answers (1)

Jens
Jens

Reputation: 69450

You have to add ENGINE=InnoDB DEFAULT CHARSET=utf8 to leads table

CREATE TABLE `leads` (
  `lead_id` int(11) NOT NULL AUTO_INCREMENT,
   ...
   PRIMARY KEY (`lead_id`)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Because if you want to add a foreign key, both tables must have the same engine

Upvotes: 4

Related Questions