CODEWITHSUNDEEP
mysqlforeign-keys
Dmitri345
Dmitri345

Reputation: 3

Error 150 when trying to import .sql file to phpmyadmin

I am trying to import a file *.sql from MySQL database on my server backup to new webhosting. I try to import via phpMyAdmin. I tried running XAMPP on local machine and import and am still getting the same error. Here is the create table statement:

CREATE TABLE `yv3nd_rokgallery_file_loves` (
   `file_id` int(10) unsigned NOT NULL DEFAULT '0',
   `kount` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`file_id`),
   UNIQUE KEY `file_id` (`file_id`),
   CONSTRAINT `yv3nd_file_loves_file_id_files_id` FOREIGN KEY (`file_id`) 
      REFERENCES `yv3nd_rokgallery_files` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I got: #1005 - Can't create table 'test.yv3nd_rokgallery_file_loves' (errno: 150)

I'm pretty new to this so any help to fix this error would be appreciated.

Here is more code:

CREATE TABLE yv3nd_rokgallery_files(  
   id int(10) unsigned NOT NULL AUTO_INCREMENT,  
   filename varchar(255) NOT NULL,  
   guid char(36) NOT NULL,  
   md5 char(32) NOT NULL,  
   title varchar(200) NOT NULL,  
   description text,  
   license varchar(255) DEFAULT NULL,  
   xsize int(10) unsigned NOT NULL, 
   ysize int(10) unsigned NOT NULL,  
   filesize int(10) unsigned NOT NULL,  
   type char(20) NOT NULL, 
   published tinyint(1) NOT NULL DEFAULT '0',  
   created_at datetime NOT NULL,  
   updated_at datetime NOT NULL,  
   slug varchar(255) DEFAULT NULL, 
   PRIMARY KEY (id), UNIQUE KEY id (id), 
   UNIQUE KEY guid (guid), 
   UNIQUE KEY yv3nd_files_sluggable_idx (slug), 
   KEY yv3nd_rokgallery_files_published_idx (published), 
   KEY yv3nd_rokgallery_files_md5_idx (md5), 
   KEY yv3nd_rokgallery_files_guid_idx (guid)) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 1415

Answers (2)

AdamMc331
AdamMc331

Reputation: 16690

The errno 150 usually means that the foreign key constraint can't be created. Without seeing the rest of the file it is hard to say for sure, but I would guess that it is one of two things:

Your yv3nd_file_loves_file_id_files_id constraint may have been defined in another table as well. You can't create two constraints with the same name, that could potentially throw an error.

Another possibility is that you are creating a constraint on incorrect types. In this table your field_id is int(10). If in the yv3nd_rokgallery_files table your id variable is anything but that this will fail. For example: if in the other table it's int(9) this won't work.

If neither of these are correct, can you post the code for the yv3nd_rokgallery_files table?

EDIT

A third possibility is that the id field in the other table is not set as the primary key. This will also cause an error, because the foreign key of one table should match the primary key of another.

EDIT 2 Another possibility could be that you are creating the file_loves table before the files table. That would cause an error because you'd be creating a reference to a table that does not exist yet.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562348

Errno 150 is what is reported if the FOREIGN KEY couldn't work.

The most common reason is that the data type of the foreign key column is different from the data type of the primary key column it references.

Your foreign key column is file_id int unsigned. The argument 10 for int(10) doesn't matter in this case. It's still an int unsigned.

Your primary key column is in table yv3nd_rokgallery_files, column id. You should double-check that that column is int unsigned. If it isn't, for example if it's a bigint or other size of int, or if it's not unsigned like file_id is, then the you'll get errno 150.

You must change one of the columns, either yv3nd_rokgallery_file_loves.file_id or yv3nd_rokgallery_files.id, so they use the same data type.

The errno 150 can also happen if the foreign key fails for other reasons. For example, table yv3nd_rokgallery_files must be an InnoDB table. Is it MyISAM?

Upvotes: 0

Related Questions