Mysql Error 1005 Hy000 At
Contents |
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 error 1005 hy000 can t create table errno 150 posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Error 1005 (hy000) (errno 150)
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 error 1005 (hy000) can't create table error 150 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(
Errno 121 Mysql
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 mysql error 1005 errno 150 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, bu
here for a quick overview of the site Help Center Detailed answers to any questions
Mysql Can't Create Table (errno: -1)
you might have Meta Discuss the workings and policies of
Error 1005 Mysql
this site About Us Learn more about Stack Overflow the company Business Learn more about hiring mysql errno -1 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 http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 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 'receitascakephp.recipes' (errno: 150) up vote 2 down vote favorite 1 CREATE TABLE `users` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, http://stackoverflow.com/questions/3128115/mysql-error-1005-hy000-cant-create-table-receitascakephp-recipes-errno `username` VARCHAR(75) NOT NULL, `password` VARCHAR(75) NOT NULL, `image` VARCHAR(255) ); CREATE TABLE `recipes` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `method` TEXT NOT NULL, `image` VARCHAR(255), `user_id` INT NOT NULL, CONSTRAINT `fk_recipes_users` FOREIGN KEY(`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; I don't know why, but I'm getting: ERROR 1005 (HY000): Can't create table 'receitascakephp.recipes' (errno: 150) mysql database mysql-error-1005 errno share|improve this question edited May 23 '11 at 2:57 bignose 10.1k63561 asked Jun 27 '10 at 17:07 Thomas 1112 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 share|improve this answer answered Jun 27 '10 at 17:15 GOsha 610512 add a comment| up vote 1 down vote Both tables need to be InnoDB: CREATE TABLE `users` ( `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `username` VARCHAR(75) NOT NULL, `password` VARCHAR(75) NOT NULL, `image` VARCHAR(255) ) ENGINE=InnoDB DEFAULT CHAR
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta http://dba.stackexchange.com/questions/62114/create-table-shows-error-1005hy000-errno-150 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 Database http://lightonphiri.org/blog/mysql-errors-error-1005-hy000-at-line-28-cant-create-table-x-errno-150 Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills error 1005 and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Create table shows ERROR 1005(HY000) errno 150 up vote 0 down vote favorite i tried to create tables as t create table per given in my textbook in MySQL server as follows: create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key (dept_name) ); create table course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department ); the department table gets created, but an error ERROR 1005 (HY000): Can't create table 'test.course' (errno: 150) is displayed. The queries seem very simple. And i am unable to find any mistake. What can be the possible error that is preventing me to create the table? mysql foreign-key share|improve this question asked Mar 31 '14 at 5:29 Amita 312 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted You are missing column name of parent table to which your child refer, while creating child table, do like below create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key (dept_name) ); create table course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department(dept_name) ); share|improve t
figure this one out. It turns out, InnoDB wants an index created on a referenced field. It turns out, the error was as a result of a non-indexed referenced field-title_etd in this case. Affected tables are below create table Title (title_etd varchar(255), oai_identifier varchar(255), primary key (oai_identifier) ); create table Subject (subject_etd varchar(255), oai_identifier varchar(255), title_etd varchar(255), constraint subject_title_etd foreign key (title_etd) references Title(title_etd), constraint subject_oai_identifier foreign key (oai_identifier) references Title(oai_identifier), primary key (oai_identifier,title_etd,subject_etd) ); MySQL InnoDB status output is below. mysql> mysql> SHOW ENGINE INNODB STATUS\G : : RW-excl spins 0, rounds 1098, OS waits 36 Spin rounds per wait: 12.35 mutex, 30.00 RW-shared, 1098.00 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 140110 13:16:42 Error in foreign key constraint of table test_etd_dbp/Subject: foreign key (title_etd) references Title(title_etd), constraint subject_oai_identifier foreign key (oai_identifier) references Title(oai_identifier), primary key (oai_identifier,title_etd,subject_etd) ): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. ------------ TRANSACTIONS ------------ Trx id counter 1321 : : 1 row in set (0.04 sec) mysql> I had to create an index on Title.title_etd to resolve this. mysql> ALTER TABLE Title ADD INDEX idx_title_etd (title_etd); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> Related Categories: Technical Tags: InnoDB, MySQL Search for: Archive General (19) Research (21) Teaching (1) Technical (38) Zambia (8) Popular Posts Subscription Leave Blank:Do Not Change:Your email: Tweet Stream https://t.co/ToI84HQPou #Africa has 37k #Wikipedia subs that need improvement. Participate in #Africa De-stubathon btw Oct 15 & Nov 27. #fb, Oct 10RT @CILT_UCT: "Scholarly research from countries in Africa does not feature globally for three primary reasons" https://t.co/DJeuBI