1

I am using phpMyAdmin. I have dropped all my tables and recreated them using the export from MySQLWorkbench (this has worked fine before). I have repopulated certain tables with data, but get a failure on this one table with:

 #1452 - Cannot add or update a child row: a foreign key constraint fails 
(`col8_apps`.`companies`, CONSTRAINT `fk_companies_users1` FOREIGN KEY (`mainadmin`) 
REFERENCES `users` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION)

The table structures concerned -

companies:

companyid     int(10)       UNSIGNED AUTO_INCREMENT
client        int(10)       UNSIGNED
name          varchar(150)
mainadmin     int(10)       UNSIGNED
....

users:

userid        int(10)       UNSIGNED AUTO_INCREMENT
....

I have a user in users with userid=1

I am trying to run this SQL:

INSERT INTO companies (companyid, client, name, mainadmin, companylogo, active) 
 VALUES (1,1,'Company One', 1, 'default',1);

If I use phpMyAdmin to Insert, it will let me select the userid from the dropdown list - so it recognises that that foreign key I'm using exists - for mainadmin but it still then gives me the foreign key error above.

Anyone any clues what might be causing this?

EDIT: Running SHOW ENGINE INNODB STATUS gave this curious message:

Trying to add to index `fk_companies_users1_idx` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 00000001; asc     ;;
1: len 4; hex 00000001; asc     ;;

But the parent table `col8_apps`.`users`
or its .ibd file does not currently exist!

The table is definitely there, but I've no idea what a .ibd file is?

EDIT to explain why not a duplicate of another question: There was a solution to this which I have added as an answer below. (this is an issue with mySqlWorkbench, not MySql (or derivative))

AntG
  • 1,291
  • 2
  • 19
  • 29
  • Your companyid is Auto-Increment so you should not need to be explicitly inserting a value - do you already have a row with companyid=1. – PaulF Jul 27 '15 at 16:19
  • No the table is empty, I have tried with and without that explicitly added, still didn't work – AntG Jul 27 '15 at 16:29
  • 1
    Possible duplicate of [MySQL 5.5 foreign key constraint fails when foreign key exists](https://stackoverflow.com/questions/5566991/mysql-5-5-foreign-key-constraint-fails-when-foreign-key-exists) – pyb Jan 10 '18 at 15:33

2 Answers2

0

Ok, so I have fixed this, the issue was that for some reason the 'users' table did not have an explicit ENGINE = InnoDB at the end of it's create statement.

I don't know why it stopped putting this for that one specific table, but adding this to the create script solved my problem.

For completeness this question shows where to set the storage engine in MySQL Workbench: MySQL Workbench EER Diagramm - how to change storage engine?

This was set to InnoDB in my model but it still wasn't including it in the export script, I set it to something else and set it back, and magically it started working correctly...

Community
  • 1
  • 1
AntG
  • 1,291
  • 2
  • 19
  • 29
0

Same problem, got it solved by emptying the table and retrying the connection. apperantly you need the the external key table to be empty

mymande
  • 21
  • 2