MySQL Incorrect Foreign Key

Question

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`.`stamp` (errno: 150 "Foreign key constraint is incorrectly formed")

when trying to create this table:

DROP TABLE IF EXISTS stamp;
CREATE TABLE stamp (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  group_id SMALLINT NOT NULL,
  member BOOLEAN NOT NULL DEFAULT FALSE,
  rank SMALLINT NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY(id),
  CONSTRAINT stamp_ibfk_1 FOREIGN KEY (group_id) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
);

What is the correct foreign key constraint for this? I presume group_id can't be used for some reason

Similar https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error — snakecharmerb, Jun 28 '20 at 17:16
*in this table, the primary key **is** the foreign key* **NO**. PK is composite whereas each FK expression is single. — Akina, Jun 28 '20 at 18:52
(Clearly,) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS You have a syntax error. Read the grammar & manual. Show that constituent subexpressions are OK. Chop out code that leaves the error. — philipxy, Jun 28 '20 at 19:38
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 28 '20 at 19:40
"in this table, the primary key is the foreign key..." -- No. A foreign key is a *pair* of column sets (one from each table), not just one set of columns. — The Impaler, Jun 28 '20 at 23:55

Bill Karwin · Answer 1 · 2020-06-28T20:15:56.040

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:

CREATE TABLE quest_award_item (
  quest_id BIGINT UNSIGNED NOT NULL,
  item_id INT NOT NULL,
  PRIMARY KEY (quest_id, item_id),
  CONSTRAINT quest_award_item_ibfk_1 FOREIGN KEY (quest_id) REFERENCES quest (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT quest_award_item_ibfk_2 FOREIGN KEY (item_id) REFERENCES item (id) ON DELETE CASCADE ON UPDATE 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.

ALTER TABLE 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.

DROP TABLE IF EXISTS quest; CREATE TABLE quest ( id SERIAL, name VARCHAR(30) NOT NULL, PRIMARY KEY(id) ); — sahmyool, Jun 28 '20 at 19:00
DROP TABLE IF EXISTS item; CREATE TABLE item ( id INT NOT NULL, name VARCHAR(50), PRIMARY KEY (id) ); — sahmyool, Jun 28 '20 at 19:01
`SERIAL` becomes `BIGINT UNSIGNED`, which is not compatible with your `INT` foreign key. — Bill Karwin, Jun 28 '20 at 19:38

score 0 · Accepted Answer · answered Jun 28 '20 at 17:12

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

DROP TABLE IF EXISTS stamp;
CREATE TABLE stamp (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  group_id INT NOT NULL,
  member BOOLEAN NOT NULL DEFAULT FALSE,
  rank SMALLINT NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY(id),
  CONSTRAINT stamp_ibfk_1 FOREIGN KEY (group_id) REFERENCES stamp_group (id) ON DELETE CASCADE ON UPDATE CASCADE
);

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

MySQL Incorrect Foreign Key

2 Answers2

Related