SQL FOREIGN KEY ERROR (errno: 150 "Foreign key constraint is incorrectly formed")-CodePudding

Hi I have this three very simple tables but I can't fix it to get the right format of foreign key.

CREATE TABLE company( company_name varchar(30) UNIQUE NOT NULL, bid INT(15) NOT NULL UNIQUE, cid INT(15) NOT NULL UNIQUE, FOREIGN KEY (bid) REFERENCES branch(branch_id), FOREIGN KEY (cid) REFERENCES contact(contact_id) );

CREATE TABLE branch( branch_id INT(15) NOT NULL AUTO_INCREMENT UNIQUE, branch_type varchar(30), cid INT(15) NOT NULL UNIQUE, PRIMARY KEY (branch_id), FOREIGN KEY (cid) REFERENCES contact(contact_id) );

CREATE TABLE contact( contact_id INT(15) NOT NULL AUTO_INCREMENT UNIQUE, fst_name varchar(20), mdl_name varchar(20), lst_name varchar(20), sex varchar(20), dob DATE, phone_number INT(15), address varchar(255), email varchar(255), bid INT(15) NOT NULL UNIQUE, PRIMARY KEY (contact_id), FOREIGN KEY (bid) REFERENCES branch (branch_id) )ENGINE=InnoDB;

All three of them have the same error150. Thank you so much for helping.table

CodePudding user response：

The problem is that you have the company table reference the branch and contact table before they are created. Also, the branch table references the contact table and vice versa so the database goes like that:

Creating the contact table ... there is a bid field connected to a table named branch ... table branch does not exist -> error

You have to create the contact table first but without the foreign id reference to bid, then create branch table and then company table. After you have your tables all set you can execute another query to add a foreign id reference to bid.

So Like this:

CREATE TABLE contact( contact_id INT(15) NOT NULL AUTO_INCREMENT UNIQUE, fst_name varchar(20), mdl_name varchar(20), lst_name varchar(20), sex varchar(20), dob DATE, phone_number INT(15), address varchar(255), email varchar(255), bid INT(15) NOT NULL UNIQUE, PRIMARY KEY (contact_id) )ENGINE=InnoDB;


CREATE TABLE branch( branch_id INT(15) NOT NULL AUTO_INCREMENT UNIQUE, branch_type varchar(30), cid INT(15) NOT NULL UNIQUE, PRIMARY KEY (branch_id), FOREIGN KEY (cid) REFERENCES contact(contact_id) );


CREATE TABLE company( company_name varchar(30) UNIQUE NOT NULL, bid INT(15) NOT NULL UNIQUE, cid INT(15) NOT NULL UNIQUE, FOREIGN KEY (bid) REFERENCES branch(branch_id), FOREIGN KEY (cid) REFERENCES contact(contact_id) );

Notice I removed FOREIGN KEY (bid) REFERENCES branch (branch_id) And then:

ALTER TABLE contact ADD FOREIGN KEY (bid) REFERENCES branch(branch_id);

P.S Run the commands in the same order