Mysql Error 1005 Errno 150 Hy000
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ERROR 1005 (HY000): Can't create table (errno: 150) up vote 12 down vote favorite 3 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) mysql share|improve this question edited Jun 15 '12 at 22:38 Austyn Mahoney 7,30243876 asked Jun 15 '12 at 5:52 Boon 1652421 Have you created sales and returnoutwards tables with unique keys? All tables must be InnoDb. –Devart Jun 15 '12 at 6:01 @Devart: Thanks, I found my mistake... it is because the receiptno in sales table is not a primary key... I should reference it to a new column with primary key, for example, sales_no –Boon Jun 15 '12 at 9:37 add a comment| 4 Answers 4 active oldest votes up vote 22 down vote accepted 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
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySql: ERROR 1005 (HY000): Can't create table '.\mydb\clientaddressdetails.frm' (errno: 150) up vote 0 down vote favorite I have used the following syntax. Please help me out in this http://stackoverflow.com/questions/19135691/mysql-error-1005-hy000-cant-create-table-mydb-clientaddressdetails-frm issue to create foreign key. mysql> create table userdetails(UserId int(3) not null, ClientId int(3) not null, UserName varchar(30), Password varchar(30) character set binary, RoleId int(3) not null, primary key(UserId, ClientId), foreign key(RoleId) references UserRoles(RoleId) on delete cascade on update cascade)ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.44 sec) mysql> create table useraddress (UserId int(3) NOT NULL, Address1 varchar(100),Address2 varchar(100), City varchar(50), Pincode varchar(10), PhoneNumber varchar(20), MobileNumber varchar(20), foreign key(UserId) references userdetails(UserId)on delete cascade on update cascade) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.09 sec) mysql> create table clientaddressdetails (Address1 varchar(100), Address2 varchar(100), City varchar(50), Pincode varchar(10), PhoneNumber varchar(20), MobileNumber varchar(20), ClientId int(3), foreign key(ClientId) references userdetails(ClientId)on delete cascade on update cascade) ENGINE=InnoDB DEFAULT CHARSET=latin1; ERROR 1005 (HY000): Can't create table '.\mydb\clientaddressdetails.frm' (errno: 150) mysql sql share|improve this question edited Oct 2 '13 at 11:23 Holger Just 26.1k65780 asked Oct 2 '13 at 11:18 Rajaah 17127 Might
for Developers MariaDB Contributor Agreement MariaDB Contributor Agreement FAQs Community Ambassadors Events Past Events and Conferences Sponsor Sponsors List of Donors Blog About MariaDB Sponsors Governance https://mariadb.org/mariadb-innodb-foreign-key-constraint-errors/ Logos and Badges MariaDB Trademark Usage Statistics Service Providers Maintenance Policy Security Policy Download Learn Get Involved Social Media Getting Started for Developers MariaDB Contributor Agreement MariaDB Contributor https://www.troyfawkes.com/solved-mysql-error-1005-cant-create-table-errno-150/ Agreement FAQs Community Ambassadors Events Past Events and Conferences Sponsor Sponsors List of Donors Blog HomeGeneralMariaDB: InnoDB foreign key constraint errors MariaDB: InnoDB foreign key constraint errors mysql error 2015-08-07 4 Comments Written by Jan Lindstrom Introduction A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose mysql error 1005 of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example: create table parent ( id int not null primary key, name char(80) ) engine=innodb; create table child ( id int not null, name char(80), parent_id int, foreign key(parent_id) references parent(id) ) engine=innodb; As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys: InnoDB (both innodb_plugin and XtraDB) PBXT (https://mariadb.com/kb/en/mariadb/about-pbxt/) SolidDB for MySQL (http://sourceforge.net/projects/soliddb/) ScaleDB (https://mariadb.com/kb/en/mariadb/scaledb/ and http://scaledb.com/pdfs/TechnicalOverview.pdf) MySQL Cluster NDB 7.3 or later (https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndb-innodb-engines.html) MariaDB fo
Communication Skills Training Interpersonal Skills Training Blog About Blog Troy Fawkes / Archives / Solved: MySQL ERROR 1005: Can't create table (errno: 150) (Foreign Key) Nov 27 Solved: MySQL ERROR 1005: Can't create table (errno: 150) (Foreign Key) November 27, 2011 Troy Fawkes 8 Comments Archives Share on Facebook Share 0 Share on TwitterTweet 0 Share on Google Plus Share 0 Share on Pinterest Share 0 Share on LinkedIn Share 0 This is another stupid error. It has to do with trying to successfully set foreign keys in MySQL. ERROR 1005: Can't create table (errno: 150) Great, that's fantastic. Here's an example of where this error will occur. CREATE TABLE main(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
);
CREATE TABLE other(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
main_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(main_id) REFERENCES main(id)
); So I'm trying to make the table "other" reference the table "main" through the foreign key "main_id" and, if you try it, it'll throw an Error 150. Want the solution? The foreign key "main_id" has to have the exact same type as the primary key that it references. In the example, "main_id" in the table "other" has the type INT NOT NULL while "id" in the table "main" has the type "INT UNSIGNED NOT NULL" and also AUTO_INCREMENT, but that isn't something we have to worry about. To make things incredibly clear, here's the working example. CREATE TABLE main( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) ); CREATE TABLE other( id INT UNSIGNED NOT NULL AUTO_INCREMENT, main_id INT UNSIGNED NOT NULL, PRIMARY KEY(id), FOREIGN KEY(main_id) REFERENCES main(id) ); To solve ‘MySQL ERROR 1005: Can't create table (errno: 150)‘ you likely just have to ensure that your foreign key has the exact same type as the primary key. Hope it helps. Share on Facebook Share 0 Share on TwitterTweet 0 Share on Google Plus Share 0 Share on Pinterest Sh