mysql - what does this error mean?

Question

Hello.

I do not require this error to be solved. I just need to understand what it means so I can approach it myself.

Cannot add or update a child row: a foreign key constraint fails
  (`db`.`transaction`, CONSTRAINT `transaction_ibfk_2`
    FOREIGN KEY (`product_id`) REFERENCES `product` (`id`))

INSERT INTO `transaction` ( `client_id`, `cost`, `website_id`, `product_id`,
    `template_number`, `value`, `order_id` )
    VALUES ( '17', '', '2480', '', '', '12', '1');

What is a foreign key? How is it set?

What does CONSTRAINT transaction_ibfk_2 mean?

Does this mean I need to have a table called transaction_ibfk_2?

Thanks.

Answer 1

You are inserting an empty string as productid (Fourth item in the list)

INSERT INTO transaction  
(client_id, cost, website_id, product_id, template_number, value, order_id) 
VALUES ('17', '', '2480', '', '', '12', '1')

There is a referential integrity constraint set up to ensure that you must only insert productids matching one in the referenced product table.

Answer 2

This means you are trying to add a value into the foreign key column which is not available in the referenced column or trying to add blank in foreign key column. i.e. You are trying to add product_id as blank which is not allowed. All values in foreign key column must be valid values present in the main referenced column id.

Answer 3

There's not necessarily a relationship between constraint names and tables although most people name them appropriately to make their lives easier.

All this means is that you have a transaction_ibfk_2 constraint. The actual problem is in the rest of the message:

FOREIGN KEY (product_id) REFERENCES product  (id)

You need to insert a row into your product table first. The id that you insert there should be the product_id you're trying to insert into transaction (which is '' for some reason - I'm pretty certain this should be a real value (or possibly NULL)).

You can create foreign keys with a clause of the create table or alter table DDL statements.