Working with foreign keys - cannot insert

Question

Hi everyone!

Doing my first tryouts with foreign keys in a mySQL database and are trying to do a insert, that fails for this reason: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Does this mean that foreign keys restrict INSERTS as well as DELETES and/or UPDATES on each table that is enforced with foreign keys relations?

Thanks!

Updated description:

Products
----------------------------
id | type
----------------------------
0     | 0
1     | 3

ProductsToCategories
----------------------------
productid | categoryid
----------------------------
0        | 0
1        | 1

Product table has following structure

CREATE  TABLE IF NOT EXISTS `alpha`.`products` (
  `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `type` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 ,    
  PRIMARY KEY (`id`) ,  
  CONSTRAINT `funkyfunky`  
    FOREIGN KEY (`id` )
    REFERENCES `alpha`.`ProductsToCategories` (`productid` )    
    ON DELETE CASCADE,    
    ON UPDATE CASCADE)  

ENGINE = InnoDB;

Answer 1

You cannot delete a row from the parent table while there is a foreign key reference to it from a child table. Also you cannot insert/update in the child table with invalid id's in the foreign key column.

Edit: The "CONSTRAINT funkyfunky FOREIGN KEY (id)" must be declared in the "ProductsToCategories" table not in the "Products" table, because "ProductsToCategories" is referencing "Products" not the opposite as you have did.

Answer 2

A foreign key enforces a valid relation between the rows in two tables. In order to be able to insert a row into a table containing a foreign key, there must be a row in the referenced table containing that key or the insert will fail. The same with delete, you can't delete the row in the referenced table while there are still rows in the table with the foreign key that still reference it. The prevents ending up with rows in the dependent table that have data, but don't have associated rows in the referenced table, i.e., a violation of referential integrity.

Answer 3

Your insert is failing because the foreign key in the row you are inserting doesn't match a valid key in the constraint table. For example:

Assume you've got these two tables:

Employees
----------------------------
EmpID | Name
----------------------------
0     | John
1     | Jane

OfficeAssignments
----------------------------
OfficeID | EmpID
----------------------------
0        | 0
1        | 1

If you have a foreign key constraint on OfficeAssignments.EmpID -> Employees.EmpID, and you try to execute:

INSERT INTO OfficeAssignments (OfficeID, EmpID) VALUES (2,2)

The statement will fail because there is no entry in the Employees table with an EmpID of 2.

Constraints are designed to ensure that your dependent table always has valid data with regard to the parent table -- in this example, you will never have an office which is listed as assigned to an employee who doesn't exist in the system, either because they never existed (as in this case) or because they've been deleted (because the constraint will prevent the employee record from being deleted until the office assignment record has been deleted first).

Edit: Now that you've posted the constraint, it indeed looks like it might be set up backwards. By placing the constraint in the definition of the Products table, you are making it the child, and ProductsToCategories the parent. The constraint you've written can be read as, "a Product must be assigned to a category before it can be created". I suspect what you meant is the other way around: "a Product must be created before it can be assigned to a category." To get that result, you need to place the constraint on the ProductsToCategories table, setting the foreign key to productid and referencing Products.id.

Answer 4

Your products table is slightly wrong, as you don't need to reference anything from it. References go in the "other" tables, and point to the main, e.g:

create table products (
  id int auto_increment,
  type int,
  primary key (id)
);

create table categories (
  id int auto_increment,
  name varchar(128),
  primary key (id)
)

create table products_to_categories (
  product_id int references products,
  category_id int references categories
);