MySQL Errno 150 (incorrect foreign key) for CREATE TABLE

Question

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)

Can you paste the entire error? what you got on mysql command line? — , Nov 15 '16 at 17:15
@ShivaShinde It's created via Java, I can add the full error in the original post. — Overt_Agent, Nov 15 '16 at 17:18

Gord Thompson · Accepted Answer · 2016-11-15T22:09:35.157

If you want to create a foreign key constraint that matches both columns in the same row of the parent table you need to specify a single FOREIGN KEY for both columns (as opposed to two separate FK constraints for each column).

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))");
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, postCode) REFERENCES Address (houseNo, postCode))");

score 1 · Answer 2 · edited May 23 '17 at 12:24

Since the Address table has no issues, this is more related to the references of Patient table and I am assuming team is the database name:

From what I see here, the referenced column should be indexed. You have created an index to both (houseNo, postCode) together, why not try to create the index to them separately and see if that works for you?
Although you have mentioned that you are using the same engine, character set etc, why not mention them explicitly in the above query and run them. Could you add, ENGINE = InnoDB in your queries?

Please let me know If it doesn't work for you.

Can't create table (errno: 150) InnoDB adding foreign key constraints

Thanks for your help. After trying your suggestions (neither of which worked, unfortunately). I've decided to compromise by adding an auto-generated ID to the address table as a singular primary key and foreign key for Patient. I'll post it as an answer (though I'm well aware it isn't really). Thanks again. — Overt_Agent, Nov 15 '16 at 18:44

score 0 · Answer 3 · answered Nov 15 '16 at 18:46

So the answer (more a compromise) I came to:

switch(tableName){
            case "Address":
                stmt.executeUpdate("CREATE TABLE Address(houseID INT(8) NOT NULL PRIMARY KEY AUTO_INCREMENT, "
                        + "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)");
                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, "
                        + "houseID INT(8) NOT NULL, "
                        + "amountOwed DECIMAL(5,2) NOT NULL, "
                        + "FOREIGN KEY (houseID) REFERENCES Address (houseID))");
                break;
}

Create an additional field in 'Address' with an ID that is the primary key and the only referenced foreign key in 'Patient'. It's not ideal, but it is functional.

MySQL Errno 150 (incorrect foreign key) for CREATE TABLE

3 Answers3