CODEWITHSUNDEEP
mysqlsqlminecraft
Power EngineTM
Power EngineTM

Reputation: 3

Cannot add foreign key constraint creating tables MySQL

The method createTable() is working, I've been creating other simple tables without issues.

I'm using MySQL server 8.0

This is a test for the tables, then I will make a script to create them. I keep having this error:

Could not create table, SQLException: Cannot add foreign key constraint

But I dont know what is wrong.

    if(!database.checkTable("maps")){
        String maps = "CREATE TABLE maps (" 
                + "name VARCHAR(32) NOT NULL,"
                + "mapType VARCHAR(32) NOT NULL,"
                + "world VARCHAR(32) NOT NULL,"
                + "referenceId INT(32) NOT NULL,"
                + "regionId INT(32) NOT NULL,"
                + "testmode BIT(1) NOT NULL,"
                + "edition BIT(1) NOT NULL,"
                + "finished BIT(1) NOT NULL,"
                + "PRIMARY KEY (name, referenceId, regionId))"
                + "ENGINE = InnoDB "
                + "DEFAULT CHARACTER SET = utf8;";
        database.createTable(maps);
    }
    if(!database.checkTable("regions")){
        String regions = "CREATE TABLE regions ("
                + "id INT(32) NOT NULL AUTO_INCREMENT," 
                + "minLocationId INT(32) NOT NULL,"
                + "maxLocationId INT(32) NOT NULL,"
                + "PRIMARY KEY (id, minLocationId, maxLocationId),"
                + "CONSTRAINT `regionMapFk`"
                + "FOREIGN KEY (id)"
                + "REFERENCES maps (regionId)"
                + "ON DELETE CASCADE "
                + "ON UPDATE CASCADE)"
                + "ENGINE = InnoDB "
                + "DEFAULT CHARACTER SET = utf8;";
        database.createTable(regions);
    }
    if(!database.checkTable("locations")){
        String locations = "CREATE TABLE locations ("   
                + "id INT(16) NOT NULL AUTO_INCREMENT, "
                + "x INT(16) NOT NULL, "
                + "y INT(16) NOT NULL, "
                + "z INT(16) NOT NULL, "
                + "PRIMARY KEY(id),"
                + "CONSTRAINT `minLocationRegionFk`"
                + "FOREIGN KEY (id)"
                + "REFERENCES regions (minLocationId)"
                + "ON DELETE CASCADE "
                + "ON UPDATE CASCADE,"
                + "CONSTRAINT `maxLocationRegionFk`"
                + "FOREIGN KEY (id)"
                + "REFERENCES regions (maxLocationId)"
                + "ON DELETE CASCADE "
                + "ON UPDATE CASCADE,"
                + "CONSTRAINT `locationMapFk`"
                + "FOREIGN KEY (id)"
                + "REFERENCES maps (referenceId)"
                + "ON DELETE CASCADE "
                + "ON UPDATE CASCADE)"
                + "ENGINE = InnoDB "
                + "DEFAULT CHARACTER SET = utf8;";
        database.createTable(locations);
    }

[23:51:36 WARN]: java.sql.SQLException: Cannot add foreign key constraint [23:51:36 WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) [23:51:36 WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) [23:51:36 WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) [23:51:36 WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) [23:51:36 WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) [23:51:36 WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) [23:51:36 WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570) [23:51:36 WARN]: at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:779) [23:51:36 WARN]: at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:622) [23:51:36 WARN]: at power.database.MySQL.createTable(MySQL.java:104) [23:51:36 WARN]: at power.managers.DatabaseManager.createTables(DatabaseManager.java:81) [23:51:36 WARN]: at power.managers.DatabaseManager.startDatabase(DatabaseManager.java:40) [23:51:36 WARN]: at power.managers.DatabaseManager.(DatabaseManager.java:30) [23:51:36 WARN]: at power.logic.ClanBattles.onEnable(ClanBattles.java:44) [23:51:36 WARN]: at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:321) [23:51:36 WARN]: at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:340) [23:51:36 WARN]: at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:405) [23:51:36 WARN]: at org.bukkit.craftbukkit.v1_8_R3.CraftServer.loadPlugin(CraftServer.java:357) [23:51:36 WARN]: at org.bukkit.craftbukkit.v1_8_R3.CraftServer.enablePlugins(CraftServer.java:317) [23:51:36 WARN]: at org.bukkit.craftbukkit.v1_8_R3.CraftServer.reload(CraftServer.java:741) [23:51:36 WARN]: at org.bukkit.Bukkit.reload(Bukkit.java:535) [23:51:36 WARN]: at org.bukkit.command.defaults.ReloadCommand.execute(ReloadCommand.java:25) [23:51:36 WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:141) [23:51:36 WARN]: at org.bukkit.craftbukkit.v1_8_R3.CraftServer.dispatchCommand(CraftServer.java:641) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.PlayerConnection.handleCommand(PlayerConnection.java:1162) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.PlayerConnection.a(PlayerConnection.java:997) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.PacketPlayInChat.a(PacketPlayInChat.java:45) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.PacketPlayInChat.a(PacketPlayInChat.java:1) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.PlayerConnectionUtils$1.run(SourceFile:13) [23:51:36 WARN]: at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [23:51:36 WARN]: at java.util.concurrent.FutureTask.run(Unknown Source) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.SystemUtils.a(SourceFile:44) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.MinecraftServer.B(MinecraftServer.java:715) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.DedicatedServer.B(DedicatedServer.java:374) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.MinecraftServer.A(MinecraftServer.java:654) [23:51:36 WARN]: at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:557) [23:51:36 WARN]: at java.lang.Thread.run(Unknown Source)

Upvotes: 0

Views: 1908

Answers (4)

M Danish
M Danish

Reputation: 478

You just need to set a unique name of all constraints. Name of constraints must unique in the whole database.

Upvotes: 0

Dupinder Singh
Dupinder Singh

Reputation: 7789

You cannot create more than one primary key in one table Like you did in Maps table

"CREATE TABLE maps (" 
                + "name VARCHAR(32) NOT NULL,"
                + "mapType VARCHAR(32) NOT NULL,"
                + "world VARCHAR(32) NOT NULL,"
                + "referenceId INT(32) NOT NULL,"
                + "regionId INT(32) NOT NULL,"
                + "testmode BIT(1) NOT NULL,"
                + "edition BIT(1) NOT NULL,"
                + "finished BIT(1) NOT NULL,"
                + "PRIMARY KEY (name, referenceId, regionId))"
                + "ENGINE = InnoDB "
                + "DEFAULT CHARACTER SET = utf8;";

and to create foreign key constraint, you have to give reference of primary key of that table, with which you want to bind your current table

have a look https://www.w3schools.com/sql/sql_foreignkey.asp

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Upvotes: 0

STaefi
STaefi

Reputation: 4377

I think you are creating the foreign keys in reverse direction which is not correct.

Since your map table has regionId then you should create a FOREIGN KEY in the map table for it, not in the region itself. So change it in the script for creating the map table this way:

FOREIGN KEY (regionId) REFERENCES regions(id)

The same applies for maxLocationId column of region table. The foreign key should be defined in the region table not in the locations itself.

Any foreign key should reference a PRIMARY KEY in the referencing table, and if it doesn't mysql would not create it.

Also you should keep a dependency order in creating tables and defining their PRIMARY KEYs, and then creating any referencing FOREIGN KEYs between them.

For example you should first create the region table and define its PRIMARY KEY. After that you should create your map table which has a reference to region and now you can define the foreign key in map table which references the id (or generally the PRIMARY KEY) of region table on its regionId column.

Please see this reference for the concept and syntax of creating foreign keys.

Upvotes: 0

Jeffry Evan
Jeffry Evan

Reputation: 327

why you have FOREIGN KEY (id) REFERENCES maps (regionId) inside your region table? it should be the other way isn't it (region table is the master table and its id referenced by table maps)?

so it should be like this inside create table maps DDL

FOREIGN KEY (regionId) REFERENCES region (id)
ON DELETE CASCADE 
ON UPDATE CASCADE

same thing with FK inside location table, i think should be the other way around

Upvotes: 0

Related Questions