1

I am trying to run some basic CREATE TABLE statements for my Databases course project and am getting some strange errors.

enter image description here

When I create the table Manuf it runs fine, but when I try to create the next table, Order, using the same syntax, it does not work.

Also, when I try to create this table, Items, I get an errno: 150. I believe this has to do with my foreign key creation, but I am not exactly sure. Here is a screenshot of that.

enter image description here

I am fairly new to using MySQL so any advice would be greatly appreciated, thank you.

Biggytiny
  • 519
  • 10
  • 29
  • 1
    @LSerni Thank you, that solved that issue haha. Not sure about the errno 150, however. – Biggytiny Nov 19 '16 at 23:33
  • 1
    Please don't use images to post textual content. You can copy from the command window and paste the text in here. Text is much easier to read, and unnecessary images make it difficult for readers using mobile devices. You also cannot copy text from an image to use when trying to help find a solution. There are many more reasons to avoid using images; you can see a list of them [in this Meta post](http://meta.stackoverflow.com/a/285557/62576). Images should only be used when there is no other way to demonstrate a problem, and that's certainly not the case here. Thanks. – Ken White Nov 19 '16 at 23:39

1 Answers1

1

The error on the Order table is caused by ORDER being a reserved word. You can specify it as `Order` with the backticks, but it's better if you choose a different name altogether.

The error 150 is related to the foreign key. The keys must be absolutely identical - the exact same definition, or the FK will fail with error 150.

Also, there must be an available index with that key definition or one compatible (see Kai Baku's example in the comment on the MySQL manual page). The same fields indexed in a different order will fail.

To begin with, check how those keys are defined in the origin tables. For example:

 test1  varchar(50) not null
 test2  varchar(50)

will not be compatible. I think that even a different collation is enough to throw FK off kilter (but this I haven't checked. The rest I'm sure of, from my personal bitter unexperience).

UPDATE: I forgot to mention, if you use InnoDB tables and issue the SHOW ENGINE INNODB STATUS, the blurb that comes out will contain a much better explanation of why the FK failed, somewhere about one third from top.

LSerni
  • 55,617
  • 10
  • 65
  • 107