MySql Error 150 - Foreign keys

To create a foreign key ,

  1. both the main column and the reference column must have same definition.
  2. both tables engine must be InnoDB.

You can alter the engine of table using this command , please take the backup before executing this command.

alter table [table name] ENGINE=InnoDB;


I had the same problem, for those who are having this also:

check the table name of the referenced table

I had forgotten the 's' at the end of my table name

eg table Client --> Clients

:)


From FOREIGN KEY Constraints

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

My suspicion is that it's because you didn't create foo as InnoDB, as everything else looks OK.

Edit: from the same page -

Both tables must be InnoDB tables and they must not be TEMPORARY tables.


You can use the command SHOW ENGINE INNODB STATUS to get more specific information about the error.

It will give you a result with a Status column containing a lot of text.

Look for the section called LATEST FOREIGN KEY ERROR which could for example look like this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
190215 11:51:26 Error in foreign key constraint of table `mydb1`.`contacts`:
Create  table `mydb1`.`contacts` with foreign key constraint failed. You have defined a SET NULL condition but column 'domain_id' is defined as NOT NULL in ' FOREIGN KEY (domain_id) REFERENCES domains (id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT contacts_teams_id_fk FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT' near ' ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT contacts_teams_id_fk FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT'.

Tags:

Mysql

Foreign Keys

Mysql Error 150

Recent Posts