CODEWITHSUNDEEP
mysql
Boon
Boon

Reputation: 727

ERROR 1005 (HY000): Can't create table (errno: 150)

I get an error when I try to create a table in mysql.

Any tips on resolving it?

create table stock_in(
    ind int not null auto_increment,
    itemcode varchar(10) not null,
    quantity int not null,
    description text not null,
    sales_ref int not null default -1,
    return_outwards_ref int not null default -1,
    stock_in_receipt_ref int not null default -1,
    date text not null,
    time text not null,
    username text not null,
    foreign key (sales_ref) references sales (receiptno),
    foreign key (return_outwards_ref) references returnoutwards(ind),
    primary key (ind)
);

The Error:

ERROR 1005 (HY000): Can't create table 'posinventory.stock_in' (errno: 150)

Upvotes: 16

Views: 60199

Answers (5)

user2285323
user2285323

Reputation: 178

If you restore your database backup you may temporary disable foreign keys check by inserting these strings in the begining of .sql file:

/*!40030 SET NAMES UTF8 */;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Upvotes: 2

Jacques Amar
Jacques Amar

Reputation: 1833

On top of other things, you need to make sure both tables/fields match CHARSET settings. A UTF type field can be substantially different size than a 'latin1'. SHOW CREATE TABLE on both tables will show you if there is a mismatch

Upvotes: 1

user1520978
user1520978

Reputation: 91

Had exactly the same problem. The source of it is in types for foreign key and reference.

Foreign key:

fer_id SMALLINT NOT NULL

and the origin field (refernce to which we provide):

id INT(11) UNSIGNED NOT NULL

I've just make the fer_id INT(11) UNSIGNED as well. Magically works!

Upvotes: 6

Withfriendship Hiox
Withfriendship Hiox

Reputation: 1297

One of the reason would be the foreign key column data type and length should be same as the referring table key column. For example the referring table column is mediumint(11) and the foreign key column is int(11) means error will occur while creating a table with foreign key.

Upvotes: 1

Bjoern
Bjoern

Reputation: 16304

Check out the MySQL manual about foreign key constrains:

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.

A few ideas:

  • Better drop the tables and create it new with a well formed syntax.
  • Make sure to add ENGINE=InnoDB; to your CREATE TABLE - command.
  • Make sure InnoDB is enabled on your MySQL server. To verify this, try this command: SHOW VARIABLES LIKE 'have_innodb'; - if it returns a YES, then InnoDB is enabled.
  • Check your command for upper- and lowercases in table- and fieldnames.
  • Check this not only one the table you want to create, but also on the tables the foreign keys are referring to.
  • Make sure your referred tables are properly indexed.

Upvotes: 30

Related Questions