Mysql error Cannot add foreign key constraint when i add foreign key

Question

IN my mysql database i have two table review and products

mysql> desc review; +---------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | review | varchar(255) | YES | | NULL | | | rating | varchar(255) | YES | | NULL | | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | | | updated_at | timestamp | NO | | CURRENT_TIMESTAMP | | | reviewer_name | varchar(255) | YES | | NULL | | | product_id | int(11) | YES | | NULL | | +---------------+--------------+------+-----+-------------------+----------------+ mysql> desc products; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | details | text | NO | | NULL | | +---------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

When i try to make product_id in review table as foreign key that refer to id in products table i found this error and the query what i gave was:

mysql> ALTER TABLE review ADD FOREIGN KEY (product_id) REFERENCES products(id); ERROR 1215 (HY000): Cannot add foreign key constraint

Please help me out what the problem is and how can i figure it out

Probably due to datatype precision mismatch between int 10 and int 11 — Gurwinder Singh
– Gurwinder Singh, Commented Apr 21, 2017 at 5:22

Bill Karwin · Accepted Answer · 2017-04-21 05:32:17Z

The "precision" of the int types means nothing. It's just a hint for display width. It makes no difference to the size of the data type.

What caused your error was that your products.id was int unsigned, but you were trying to declare a foreign key for review.product_id which was int (signed).

The difference between signed and unsigned int was what makes the data types incompatible for referential integrity. You could make one int(10) unsigned and the other int(327) unsigned and it would be okay.

Mayank Pandeyz · Accepted Answer · 2017-04-21 05:26:09Z

1

There may be two reasons for the error.

The datatype precision is different for two columns i.e.

products -> id | int(10) unsigned // here int(10) product_id | int(11) // here int(11)

make the precision same by altering the table structure

To create foreign key relation ship the parent table column must be unique or a primary column.

So check if products(id) is primary or unique, if not make it primary or unique.

answered Apr 21, 2017 at 5:26

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

3 Comments

Karthiga Over a year ago

I need another help when i try to insert values inside review table

mysql> insert into review(review,rating,reviewer_name,product_id) values ('Products are good','4','John Deo','1'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (

birdys_dev.review, CONSTRAINT review_ibfk_1` FOREIGN KEY (product_id) REFERENCES products (id)) `

Karthiga Over a year ago

Could you please help me

Mayank Pandeyz Over a year ago

With foreign key relationship, you can only insert those values in child table which are already present in parent table for the foreign key binding columns

Community · Accepted Answer · 2017-05-23 11:47:01Z

alter your table structure

id | int(11)

or set

product_id | int(10) unsigned

do which way is batter for you. also you can also get better understanding about FOREIGN KEY from here

Collectives™ on Stack Overflow

Mysql error Cannot add foreign key constraint when i add foreign key

3 Answers 3

Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Linked

Related