Error in mysql - ERROR 1215 (HY000): Cannot add foreign key constraint

Question

I want to create table with two foreign key column reference to ID and stocks of Product Detail table

Reference table Product Detail:

mysql> describe productDetail;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field        | Type         | Null | Key | Default           | Extra                       |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ProductName  | varchar(255) | YES  |     | NULL              |                             
|
| productPrice | double       | YES  |     | NULL              |                             |
| GST          | int(25)      | YES  |     | NULL              |                             |
| stocks       | int(11)      | NO   | PRI | NULL              |                             |
| date         | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ID           | int(11)      | NO   | PRI | NULL              | auto_increment              |
+--------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

Table Where i have to set foreign key :

mysql> describe TodayProduction;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| TP_ID     | int(11) | NO   | MUL | NULL    |       |
| TP_Stocks | int(11) | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I have successfully add foreign key to the TP_ID reference to ID of productDetail but i cant add foreign key to the TP_Stocks reference to stocks column of productDetail

Query to add foreign key

alter table TodayProduction add constraint fk_Stocks foreign key (TP_Stocks) references productDetail(stocks);

Error

ERROR 1215 (HY000): Cannot add foreign key constraint

SHOW ENGINE INNODB STATUS;

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.

You might like to review this checklist for foreign keys: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Feb 07 '21 at 15:20

score 2 · Answer 1 · answered Feb 07 '21 at 15:17

You seem to have a composite primary key. You need to use all components in the foreign key reference:

alter table TodayProduction add constraint fk_Stocks
    foreign key (productdetail_id, TP_Stocks) references productDetail (id, stocks);

That said, I don't understand why you have a composite key. Usually a column called id would be the complete primary key.

score 1 · Answer 2 · answered Feb 07 '21 at 15:20

1

Add a index to your Table productDetail

KEY(stocks)

you need a index for all columns.

If you have a combined index you also need ti reference the the combined index columns.

answered Feb 07 '21 at 15:20

nbk

45,398
8
30
47

Error in mysql - ERROR 1215 (HY000): Cannot add foreign key constraint

2 Answers2