-1

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

Karthiga
  • 859
  • 2
  • 17
  • 33

3 Answers3

2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
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.

Mayank Pandeyz
  • 25,704
  • 4
  • 40
  • 59
  • 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 Apr 21 '17 at 05:38
  • Could you please help me – Karthiga Apr 21 '17 at 05:39
  • 1
    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 – Mayank Pandeyz Apr 21 '17 at 05:41
0

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

Community
  • 1
  • 1
Bhupat Bheda
  • 1,968
  • 1
  • 8
  • 13