0

I'm trying to create a table with foreign keys. When I execute the CREATE TABLE Statement as below, I got this error message.

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk_opentran_helper' in the referenced table 'helper'

CREATE TABLE Statement

CREATE TABLE helper_work_day(
work_day_id TINYINT,
helper_id SMALLINT(4) NOT NULL,
office_code DECIMAL(10) NOT NULL,
work_day TINYINT NOT NULL,
kana VARCHAR(32) NOT NULL,
can_holiday_work BIT(1) NOT NULL,
CONSTRAINT pk_open_transaction PRIMARY KEY (office_code, helper_id, work_day_id),
CONSTRAINT fk_opentran_helper FOREIGN KEY (helper_id) references helper (id),
CONSTRAINT fk_opentran_office FOREIGN KEY (office_code) references office (office_code)
);

Information of 'helper' TABLE

mysql> SHOW COLUMNS FROM helper;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| id               | smallint(4)   | NO   | PRI | NULL    |       |
| office_code      | decimal(10,0) | NO   | PRI | NULL    |       |
| name             | varchar(32)   | NO   |     | NULL    |       |
| kana             | varchar(32)   | NO   |     | NULL    |       |
| can_holiday_work | bit(1)        | NO   |     | NULL    |       |
| login_id         | varchar(16)   | NO   |     | NULL    |       |
| password         | char(60)      | NO   |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+
7 rows in set (0.04 sec)

mysql> SHOW INDEX FROM helper;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| helper |          0 | PRIMARY  |            1 | office_code | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| helper |          0 | PRIMARY  |            2 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.06 sec)

What I've tried

  • Make sure the 'id' column in the 'helper' table has an index
  • Make sure the 'id' column('helper' table) and 'helper_id' column('helper_work_day' table) have the same data type
hallen0328
  • 31
  • 5
  • Looks you have composite primary index based on two columns (id and office_code). For foreign key you need separate index for id column. – Slava Rozhnev Mar 07 '20 at 20:19
  • Thank you for your comment! As you said, I added the index for id column. I succeeded in creating the table. – hallen0328 Mar 07 '20 at 20:25
  • See: [can-a-foreign-key-reference-a-non-unique-index](https://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index) – Paul Spiegel Mar 07 '20 at 20:57

1 Answers1

0

Thanks to Slava Rozhnev's comment, I succeeded in creating this table. I added the index for id column(in the'helper' table) with this statement.

ALTER TABLE helper ADD INDEX index_id(id);

hallen0328
  • 31
  • 5