A foreign key constraint fails with Zend_Db_Table

Question

I have to tables and their SQL is as below:

CREATE TABLE item (
itemID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
price FLOAT NOT NULL,
stock INTEGER NOT NULL
) ENGINE = InnoDB;


CREATE TABLE salesrecord (
recordID INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
itemID INT UNSIGNED NOT NULL,
recordDate DATE NOT NULL,
FOREIGN KEY (itemID) REFERENCES item (itemID)
) ENGINE = InnoDB;

And this is my Zend_Db_Table concrete classes:

<?php
class Store_Model_Items extends Zend_Db_Table_Abstract
{
  protected $_name = 'item';
  protected $_rowClass = 'Store_Model_Item';
  protected $_dependTables = array('Store_Model_SalesRecords');
}


<?php
class Store_Model_SalesRecords extends Zend_Db_Table_Abstract
{
  protected $_name = 'salesrecord';
  protected $_referenceMap = array(
    'Item' => array(
        'columns' => array('itemID'),
        'refTableClass' => 'Store_Model_Items',
        'refColumns' => array('itemID'),
        'onDelete' => self::CASCADE
    )
  );
}

When I try to delete a row in item table, I get this message:

SQLSTATE[HY000]: General error: 1451 Cannot delete or update a parent row: a foreign key constraint fails (newstore/salesrecord, CONSTRAINT salesrecord_ibfk_1 FOREIGN KEY (itemID) REFERENCES item (itemID))

score 0 · Accepted Answer · answered Aug 25 '11 at 05:25

If you want delete the record from parent table, firstly you should delete related records in a child table. Also you can add ON DELETE CASCADE checking, it will help automatically delete related records in the child table. Try to recreate your FK in this way -

ALTER TABLE salesrecord
  DROP FOREIGN KEY salesrecord_ibfk_1; -- change FK name here!

ALTER TABLE salesrecord
  ADD CONSTRAINT salesrecord_ibfk_1 FOREIGN KEY (itemID)
    REFERENCES item(itemID) ON DELETE CASCADE;

Change 'salesrecord_ibfk_1' with actual FK name.

A foreign key constraint fails with Zend_Db_Table

1 Answers1