MySQL Foreign Key Constraint Is Incorrectly Formed Error

mysql Foreign key constraint is incorrectly formed error

I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length.

The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them both the same exact type, the foreign key creation worked perfectly.

MySQL - Error: 150 Foreign key constraint is incorrectly formed)

You are creating a foreign key on addon_account_data(account_name) that references addon_account(name). You have a composite primary the referred table : addon_account(id, name).

This is not allowed in MySQL, as explained in the documentation:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

Possible solutions:

• add an additional column in the referring table: addon_account_data(account_id, account_name) and create a composite primary key to the corresponding columns in addon_account

• create an index on addon_account(name) (probably the simplest solution)

• change the order of the columns in the primary key of the referred table, like: addon_account(name, id) (you might want to first consider the impacts this may have in terms of performance)

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

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

Remove primary key(s) - Foreign key constraint is incorrectly formed

The index is still needed for the existing FK constraints.

Adding the following index (first) should satisfy that requirement:

CREATE INDEX xxx ON employee (User, Company);

Test case

How to solve the errno: 150 Foreign key constraint is incorrectly formed even though my constraint is correct?

The primary key of Rooms is two columns: (HotelID, Room_Number).

The foreign key in BookingInfo is one column: (Room_Number).

A foreign key should reference the whole primary key of the referenced table. If that primary key has more than one column, all of the columns must be referenced by the foreign key, and in the same order.

---

Caveat that is not standard SQL and specific to InnoDB: InnoDB allows a foreign key to reference part of a multi-column key in the referenced table, as long as the column is the leftmost column of that key. But in your case, you are referencing the second from the left column of that key, Room_Number. So it's not allowed. And in my opinion, it's a bad idea anyway to reference part of the key, because it leads to weird data anomalies.

errno 150: Foreign key constraint is incorrectly formed

You need to remove NOT NULL from authorID:

CREATE TABLE post
(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    authorID INT ,   -- here NOT NULL was removed
    imgPath VARCHAR(500),
    postText TEXT NOT NULL,
    CONSTRAINT post_user_id_fk FOREIGN KEY (authorID) 
     REFERENCES user (id)  ON DELETE SET NULL
);

DBFiddle Demo

ON DELETE SET NULL and NOT NULL column are not compatible.

SQL foreign keys incorrectly formed

1. The tables creation order is critical - you cannot refer to the table which is not created. So create Users firstly then Orders.
2. Your referencing columns datatypes are not compatible - Users.UserID is defined as Varchar(255) whereas Orders.User_id is defined as Int(255). You must set the same datatype in both tables. For id column INT datatype seems to be the most reasonable.

PS. Int(255) is not safe, INTEGER datatype cannot store 255 digits. And the length specifying will be ignored anycase. Moreover, it is deprecated, so remove it at all.

---

Related Topics