-1

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

Toby
  • 5
  • 2
  • Ask 1 (specific researched non-duplicate) question. A question re how you are 1st stuck will be a duplicate. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. Please in code questions give a [mre]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy May 14 '22 at 00:04
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) Please look at the formatted version of your post before you publish. See the edit help re inline & block code & quotations & line breaks. – philipxy May 14 '22 at 00:04
  • Does this answer your question? [mysql Foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) – philipxy May 14 '22 at 00:05

1 Answers1

0

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

Fadi Sarwat
  • 46
  • 4