Mysql Incorrect Foreign Key

MySQL (mariadb Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2) gave me this error: ERROR 1005 (HY000) at line 129: Can't create table `Houdini`

Solution 1:

The foreign keys are members of the primary key of the quest_award_item table, so they must be the same data type as themselves, but that's not the problem.

You seem to think that they must have the same data type as the primary key of their own table, but this is in fact not required. The requirement is that foreign key column(s) must have the same data type as the column(s) they reference.

In this case, quest_award_item.quest_id must be the same data type as quest.id.

Likewise, quest_award_item.item_id must be the same data type as item.id.

You haven't shown the table definitions of the quest or item tables, so we can only guess at their data types. But one or other other may have an incompatible data type.

---

Re your comment:

So how do I fix this?

You posted in a comment below that the quest.id column is defined as SERIAL, which MySQL translates into BIGINT UNSIGNED AUTO_INCREMENT.

The foreign key column that references quest.id must be the same data type as the column it references (the AUTO_INCREMENT part is not necessary to match the data type).

So change your CREATE TABLE:

CREATETABLE quest_award_item (
  quest_id BIGINT UNSIGNED NOTNULL,
  item_id INTNOTNULL,
  PRIMARY KEY (quest_id, item_id),
  CONSTRAINT quest_award_item_ibfk_1 FOREIGN KEY (quest_id) REFERENCES quest (id) ONDELETE CASCADE ONUPDATE CASCADE,
  CONSTRAINT quest_award_item_ibfk_2 FOREIGN KEY (item_id) REFERENCES item (id) ONDELETE CASCADE ONUPDATE CASCADE
);

An alternative solution is to modify the quest.id to be an INT, and then your original CREATE TABLE quest_award_item will work.

ALTERTABLE quest MODIFY COLUMN id INT AUTO_INCREMENT;

But if you already have rows in that table with id values great enough that they need to be BIGINT UNSIGNED (i.e. greater than 2-1), then you can't do that.

Solution 2:

could be the data type between the two key are not the same

DROPTABLE IF EXISTS stamp;
CREATETABLE stamp (
  id INTNOTNULL,
  name VARCHAR(50) NOTNULL,
  group_id INTNOTNULL,
  memberBOOLEANNOTNULLDEFAULTFALSE,
  rank SMALLINTNOTNULLDEFAULT1,
  description VARCHAR(255) NOTNULLDEFAULT'',
  PRIMARY KEY(id),
  CONSTRAINT stamp_ibfk_1 FOREIGN KEY (group_id) REFERENCES stamp_group (id) ONDELETE CASCADE ONUPDATE CASCADE
); 

if you use int for id you should use int for group_id