niallmcfc niallmcfc - 19 days ago 7
Java Question

MySQL Errno 150 (incorrect foreign key) for CREATE TABLE

I acknowledge this has been asked several times here, and I've looked through a great many of the answers.

I know that errno150 refers to a table with an incorrect foreign key, and I've seen a previous answer here. I've checked every condition there and can't figure this out. Don't know if I'm missing something obvious.

This is using the java implentation of MySQL, I've a switch statement that creates two tables (at the moment) like so:

//Creates the table with the name given to the function and adds all fields & keys
switch(tableName){
case "Address":
stmt.executeUpdate("CREATE TABLE Address(houseNo INT(4) NOT NULL, "
+ "firstLine VARCHAR(30) NOT NULL, "
+ "secondLine VARCHAR(30), "
+ "city VARCHAR(25) NOT NULL, "
+ "county VARCHAR(25) NOT NULL, "
+ "postCode VARCHAR(7) NOT NULL, "
+ "PRIMARY KEY (houseNo, postCode))");
break;
case "Patient":
stmt.executeUpdate("CREATE TABLE Patient(patientID INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT, "
+ "title VARCHAR (10) NOT NULL, "
+ "forename VARCHAR(15) NOT NULL, "
+ "surname VARCHAR(25) NOT NULL, "
+ "dob DATE NOT NULL, "
+ "phoneNo CHAR(11) NOT NULL, "
+ "houseNo INT(4) NOT NULL, "
+ "postCode VARCHAR(7) NOT NULL, "
+ "amountOwed DECIMAL(5,2) NOT NULL, "
+ "FOREIGN KEY (houseNo) REFERENCES Address (houseNo), "
+ "FOREIGN KEY (postCode) REFERENCES Address (postCode))");
break;
}


Address creates fine (no foreign key), Patient gives errno150. Same engine, same charset, same data type (even copied an pasted to be 100% sure), Newly created (empty) tables, both non temporary.

Thanks.

P.S. I can't use
SHOW ENGINE INNODB STATUS
as I've not got a high enough permission level.

Full error message:

java.sql.SQLException: Can't create table 'team.Patient' (errno: 150)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)
at uk.team.App.makeTable(App.java:146)
at uk.team.App.setupEnviro(App.java:80)
at uk.team.App.access$1(App.java:45)
at uk.team.App$1.run(App.java:33)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Answer

MySQL can't create the foreign key constraint for postCode because the Address table has no index where the first column is postCode. (PRIMARY can serve as the index for houseNo in Address because houseNo is the first column in that index.)

This works for me with MySQL 5.6.13:

stmt.executeUpdate("CREATE TABLE Address(houseNo INT(4) NOT NULL, "
        + "firstLine VARCHAR(30) NOT NULL, "
        + "secondLine VARCHAR(30), "
        + "city VARCHAR(25) NOT NULL, "
        + "county VARCHAR(25) NOT NULL, "
        + "postCode VARCHAR(7) NOT NULL, "
        + "PRIMARY KEY (houseNo, postCode), "
        + "KEY postCode (postCode) "  // <--- new
        + ")");
stmt.executeUpdate("CREATE TABLE Patient(patientID INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT, "
        + "title VARCHAR (10) NOT NULL, "
        + "forename VARCHAR(15) NOT NULL, "
        + "surname VARCHAR(25) NOT NULL, "
        + "dob DATE NOT NULL, "
        + "phoneNo CHAR(11) NOT NULL, "
        + "houseNo INT(4) NOT NULL, "
        + "postCode VARCHAR(7) NOT NULL, "
        + "amountOwed DECIMAL(5,2) NOT NULL, "
        + "FOREIGN KEY (houseNo) REFERENCES Address (houseNo), "
        + "FOREIGN KEY (postCode) REFERENCES Address (postCode))");
Comments