0

I have two tables Plane and PlaneType. 

 CREATE TABLE Plane 
(
    ID INT,
    Maker VARCHAR(30),
    Model VARCHAR(30),
    LastMaint VARCHAR(30),
    LastMaintA VARCHAR(3),

    PRIMARY KEY (ID)
) ENGINE = INNODB;

CREATE TABLE PlaneType (
    Maker VARCHAR(30),
    Model VARCHAR(30),

    PRIMARY KEY (Maker, Model)
    ) ENGINE = INNODB;

After I insert these two tables I add the following foreign and primary keys:

ALTER TABLE Plane ADD FOREIGN KEY (Maker) REFERENCES PlaneType(Maker);
ALTER TABLE Plane ADD FOREIGN KEY (Model) REFERENCES PlaneType(Model);

The first ALTER works perfectly but the second gives me the error: 

 ERROR 1005 (HY000): Can't create table '----.frm' (errno: 150)

I cannot figure out why the first ALTER works perfectly and the second doesn't. Both are references to primary keys, and that's the only issue I could think off. I'm a mysql noob so it might be something very obvious.

Any help is appreciated, thanks in advance.

Kenshin
  • 177
  • 1
  • 9
  • was about to answer before marked as dupe: having `maker` and `model` could be avoided if you make the PK of `PlaneType` an `AUTO_INCREMENT INT` column called, say, `plane_type_id`. Then `Plane` would have a single `plane_type_id` that could be used to `JOIN` on `PlaneType` to pull the maker and model. –  Feb 14 '16 at 04:26
  • Thanks for the suggestion, I'll give that a try as well. – Kenshin Feb 14 '16 at 04:28

1 Answers1

1

Try this SHOW ENGINE INNODB STATUS and look for the LATEST FOREIGN KEY ERROR to get the specific error.

But I do believe it's a problem of indexes. Your primary key consists of Maker AND Model. Create an index on Model and the FK will be created.

Edu C.
  • 398
  • 1
  • 8
  • Thanks a lot! That answered my question perfectly. – Kenshin Feb 14 '16 at 04:27
  • @Kenshin This just answer the question and solves the error, though. Do notice that you might have problems later as a composite PK should have a composite FK (as in, the FK should have the same number of columns as the PK), read more in: http://stackoverflow.com/questions/16323676/referencing-a-composite-primary-key. – Edu C. Feb 14 '16 at 04:30