Mysql Error 1005 Hy000
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss error 1005 (hy000) (errno 150) the workings and policies of this site About Us Learn more about
Error 1005 (hy000) Can't Create Table Error 150
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow error 1005 (hy000): can't create table "(errno: -1)" 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
Errno 121 Mysql
other. Join them; it only takes a minute: Sign up MySQL “ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)” up vote 38 down vote favorite 5 I was working on creating some tables in database foo, but every time I end up with errno 150 regarding the foreign key. Firstly, here's my code for creating tables: CREATE TABLE mysql "(errno: -1)" Clients ( client_id CHAR(10) NOT NULL , client_name CHAR(50) NOT NULL , provisional_license_num CHAR(50) NOT NULL , client_address CHAR(50) NULL , client_city CHAR(50) NULL , client_county CHAR(50) NULL , client_zip CHAR(10) NULL , client_phone INT NULL , client_email CHAR(255) NULL , client_dob DATETIME NULL , test_attempts INT NULL ); CREATE TABLE Applications ( application_id CHAR(10) NOT NULL , office_id INT NOT NULL , client_id CHAR(10) NOT NULL , instructor_id CHAR(10) NOT NULL , car_id CHAR(10) NOT NULL , application_date DATETIME NULL ); CREATE TABLE Instructors ( instructor_id CHAR(10) NOT NULL , office_id INT NOT NULL , instructor_name CHAR(50) NOT NULL , instructor_address CHAR(50) NULL , instructor_city CHAR(50) NULL , instructor_county CHAR(50) NULL , instructor_zip CHAR(10) NULL , instructor_phone INT NULL , instructor_email CHAR(255) NULL , instructor_dob DATETIME NULL , lessons_given INT NULL ); CREATE TABLE Cars ( car_id CHAR(10) NOT NULL , office_id INT NOT NULL , engine_serial_num CHAR(10) NULL , registration_num CHAR(10) NULL , car_make CHAR(50) NULL , car_model CHAR(50) NULL ); CREATE TABLE Offices ( office_id INT NOT NULL , off
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
Mysql Can't Create Table (errno: -1)
site About Us Learn more about Stack Overflow the company Business Learn
Mysql Error 1005 Errno 150
more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x error 1005 mysql 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 MySql: http://stackoverflow.com/questions/2799021/mysql-error-1005-hy000-cant-create-table-foo-sql-12c-4-errno-150 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 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 http://stackoverflow.com/questions/19135691/mysql-error-1005-hy000-cant-create-table-mydb-clientaddressdetails-frm 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 need to create an index. See this article stackoverflow.com/questions/4063141/… –asantaballa Oct 2 '13 at 11:21 Indeed, a ALTER TABLE userdetails ADD INDEX(ClientId); & it works, foreign keys need to have an index in the referenced table (may be compound, but then it needs to be the first field in the index). –Wrikken Oct 2 '13 at 11:29 Thank you so much @asantaballa Solved! –Rajaah Oct 2 '13 at 11:30 T
Goff Email Updates: Status: Closed Impact on me: None Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious) Version:5.0, 5.1, 5.1.47 OS:Linux (Linux) Assigned to: Kevin Lewis Triage: Triaged: D4 (Minor) View Add Comment Files Developer Edit Submission View Progress Log Contributions [29 Nov 2005 18:58] Eric Goff https://bugs.mysql.com/bug.php?id=15324 Description: If I try to alter a table to add a foreign key on an INNODB table, I get an error. ERROR 1005 (HY000): Can't create table './mndb/#sql-992_406.frm' (errno: 150) How to repeat: create table foo(id integer) ; create table bar(id integer) https://www.troyfawkes.com/solved-mysql-error-1005-cant-create-table-errno-150/ ; alter table foo add foreign key(id) references bar(id) ; returns: ERROR 1005 (HY000): Can't create table './mndb/#sql-992_406.frm' (errno: 150) It appears that MySQL is generating a temporary table with '#' in it. I see that in general, there is a error 1005 bug that innodb can not deal with # in table names. I would argue that that is a different bug since it was talking about tables that the user generates. I have no control over these temporary tables, and therefore no work-around at all. Suggested fix: Make it work. :) Or at least change the temporary table to not use #. [29 Nov 2005 20:36] Jorge del Conde Hi! I was unable to reproduce this problem under FC4 / 5.0.16: Welcome to the MySQL error 1005 (hy000) monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.16 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table foo(id integer) ; create table bar(id integer) ; Query OK, 0 rows affected (0.02 sec) mysql> create table bar(id integer) ; alter table foo add foreign key(id) references bar(id) ; Query OK, 0 rows affected (0.01 sec) mysql> alter table foo add foreign key(id) references bar(id) ; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> [29 Nov 2005 20:44] Eric Goff Are you sure you used INNODB engine ? I should have been more explicit with my SQL, please try: create table foo(id integer) engine INNODB ; create table bar(id integer) engine INNODB ; alter table foo add foreign key(id) references bar(id) ; [8 Mar 2007 17:21] anoop kulkarni Is this still an issue? We keep coming across this bug off and on.Eric's last example doesnt work on our mysql db. Engine: INNODB version: 5.0.33 [25 Mar 2009 8:10] Mike Cook We have found a definite cause of this problem (even if it isn't the only one.) If you try and create a foreign key constraint with the same name as an existing one it will fail with a 'constraint already exists' error. After this you can no longer create constraints to the same parent table and receive the errno:150 error. If you then drop the parent table and recrea
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 Share 0 Share on LinkedIn Share 0 Facebook