Error 1005 Errno 140
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 error 1005 errno 150 company Business Learn more about hiring developers or posting ads with us Stack Overflow
Error 1005 Errno 121
Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7
Mysql Error 1005 Errno 150
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
Sql Error 1005 Errno 150
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) error 1005 (hy000) errno 150 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,26943875 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 sur
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and mysql error code 1005 errno 121 policies of this site About Us Learn more about Stack Overflow the error 1005 hy000 can t create table errno 150 company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags error 1005 (hy000) can't create table error 150 Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150 a minute: Sign up MySQL error 1005 errno 150 Foreign key constraint is incorrectly formed up vote 0 down vote favorite I have created the following tables: Stops: CREATE TABLE Stop ( routeNo DECIMAL (4,0) UNSIGNED, stopNo DECIMAL(3,0) UNSIGNED, latitude DECIMAL(19,16), longitude DECIMAL(19,16), CONSTRAINT PK_Location PRIMARY KEY (routeNo, stopNo), CONSTRAINT FK_stop_location FOREIGN KEY (latitude,longitude) REFERENCES Location(latitude,longitude), CONSTRAINT FK_stop_route FOREIGN KEY (routeNo) http://stackoverflow.com/questions/34074739/mysql-error-1005-errno-150-foreign-key-constraint-is-incorrectly-formed REFERENCES Route(routeNo) ); And the table WorkDay: CREATE TABLE Workday ( theDate DATE PRIMARY KEY, notes VARCHAR(30) ); I then try to create the table Order Delivery with the following code: CREATE TABLE OrderDelivery ( routeNo DECIMAL(4,0) UNSIGNED, stopNO DECIMAL(3,0) UNSIGNED, orderNo INT(9), CONSTRAINT pk_Orders PRIMARY KEY (routeNo, stopNo, orderNo), deliveryDateExpected DATE, deliveryTime TIME, customerName VARCHAR(40), creditCardNo CHAR(16) NOT NULL, customerRanking ENUM('platinum','gold','occasional','one-off'), CONSTRAINT fk_Routes FOREIGN KEY (routeNo) REFERENCES Stop(routeNo), CONSTRAINT fk_Stops FOREIGN KEY (stopNo) REFERENCES Stop(stopNo), CONSTRAINT fk_Dates FOREIGN KEY (deliveryDateExpected) REFERENCES Workday(theDate) ); However every time i try to create the OrderDelivery table it gives the error that the foreign key is incorrectly formed "SQL Error (1005): Can't create table 'harry.OrderDelivery' (errno:150)Foreign key constraint is incorrectly formed" How can i rectify this? mysql share|improve this question asked Dec 3 '15 at 19:40 HarAdams 92 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote You can't create a foreign key to a table with a multiple primary key referencing only one field of that primary key. So your problem lies here: CONSTRAINT fk_Routes FOREIGN KEY
Reporter: Jonathan Miller Email Updates: Status: Closed Impact on me: None Category:MySQL Server: Documentation Severity:S3 (Non-critical) Version:5.1.6-alpha OS:Linux (Linux) Assigned https://bugs.mysql.com/bug.php?id=16793 to: Jon Stephens Triage: D4 (Minor) View Add Comment Files Developer Edit https://bugs.mysql.com/bug.php?id=16310 Submission View Progress Log Contributions [26 Jan 2006 2:42] Jonathan Miller Description: mysqltest: At line 56: query 'CREATE TABLE test.t2 ( c1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 TEXT NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, c5 DECIMAL(8,3), c6 LONGBLOB, PRIMARY KEY(c1,c4)) TABLESPACE range_ts2 STORAGE error 1005 DISK ENGINE=NDB PARTITION BY RANGE (c1) PARTITIONS 3 ( PARTITION x1 VALUES LESS THAN (200), PARTITION x2 VALUES LESS THAN (400), PARTITION x3 VALUES LESS THAN (600))' failed: 1005: Can't create table 'test.t2' (errno: 140) (the last lines may be the most important ones) How to repeat: See above Suggested fix: Either support bit as part of multi PK or error 1005 errno return error message telling of the infraction [6 Feb 2006 10:19] Jonas Oreland 1) ndb does not support bit field as primary key (neither as single pk, nor composite) 2) I think that error messages are ok. 3) I'm not sure that it's in the documentation Conclusion: Documenting mysql> create table t1 (a bit(1) primary key); ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 140) mysql> show warnings; +-------+------+---------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------+ | Error | 1296 | Got error 739 'Unsupported primary key length' from NDB | | Error | 1005 | Can't create table 'test.t1' (errno: 140) | +-------+------+---------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> Aborted jonas@perch:~/run/2-node/api.6> perror 140 MySQL error code 140: Wrong create options [6 Feb 2006 23:55] Mike Hillyer Documenting status is just for flagging that a bug needs a changelog entry, changing status to Verified and assigning to Jon Stephens. [7 Feb 2006 10:16] Jon Stephens Um, no. "Documenting" means this needs to be documented. Since this is a bug against the docs, there's n
Jonathan Miller Email Updates: Status: Closed Impact on me: None Category:MySQL Server Severity:S3 (Non-critical) Version:5.1.5-alpha OS:Linux (Linux) Assigned to: Jonathan Miller View Add Comment Files Developer Edit Submission View Progress Log Contributions [9 Jan 2006 22:41] Jonathan Miller Description: http://bugs.mysql.com/bug.php?id=16309 mysql> CREATE TABLE mysqltest.t1 (c1 INT, c5 TEXT, PRIMARY KEY (c1)) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; ERROR 1005 (HY000): Can't create table 'mysqltest.t1' (errno: 155) perror --ndb 155 OS error code 155: No message slogan found (please report a bug if you get this error code): Unknown: Unknown perror 155 Illegal error code: 155 perror 1005 Illegal error code: 1005 perror --ndb 1005 OS error code 1005: No message slogan found (please report a bug if you get this error code): Unknown: Unknown How to repeat: mysql> DROP DATABASE IF EXISTS mysqltest; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE mysqltest; Query OK, 1 row affected (0.00 sec) mysql> CREATE LOGFILE GROUP lg1 -> ADD UNDOFILE 'undofile.dat' -> INITIAL_SIZE 3M -> UNDO_BUFFER_SIZE = 1M -> ENGINE=NDB; Query OK, 0 rows affected (0.83 sec) mysql> CREATE TABLESPACE ts1 -> ADD DATAFILE 'datafile.dat' -> USE LOGFILE GROUP lg1 -> INITIAL_SIZE 3M -> ENGINE NDB; Query OK, 0 rows affected (0.72 sec) mysql> CREATE TABLE mysqltest.t1 (c1 INT, c5 TEXT, PRIMARY KEY (c1)) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; ERROR 1005 (HY000): Can't create table 'mysqltest.t1' (errno: 155) mysql> CREATE TABLE mysqltest.t1 (c1 INT, c6 LONGBLOB, PRIMARY KEY (c1)) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; ERROR 1005 (HY000): Can't create table 'mysqltest.t1' (errno: 155) [10 Jan 2006 7:38] Jonas Oreland Since parent bug is closed...i cant reproduce this one... [10 Jan 2006 14:10] Jonathan Miller ~/jmiller/builds/bin/perror 10