Declare DRI in the database:
Declaring cascading operations in
Zend_Db_Table is intended
only for RDBMS brands that do not support
declarative referential integrity (DRI).
For example, if you use MySQL's or MariaDB's MyISAM storage engine, or SQLite, these solutions
do not support DRI. You may find it helpful to declare the
cascading operations with
If your RDBMS implements DRI and the
DELETE and ON
UPDATE clauses, you
should declare these clauses in your database schema, instead of using the cascading
Zend_Db_Table. Declaring cascading
DRI rules in the RDBMS is better for database
performance, consistency, and integrity.
Most importantly, do not declare cascading operations both in the
RDBMS and in your
You can declare cascading operations to execute against a dependent table when you
UPDATE or a
DELETE to a row in a
Example 334. Example of a Cascading Delete
This example shows deleting a row in the Products table, which is configured to automatically delete dependent rows in the Bugs table.
$productsTable = new Products();
$productsRowset = $productsTable->find(1234);
$product1234 = $productsRowset->current();
// Automatically cascades to Bugs table
// and deletes dependent rows.
Similarly, if you use
UPDATE to change the value of a primary key
in a parent table, you may want the value in foreign keys of dependent tables to be
updated automatically to match the new value, so that such references are kept up to
It's usually not necessary to update the value of a primary key that was generated by a sequence or other mechanism. But if you use a natural key that may change value occasionally, it is more likely that you need to apply cascading updates to dependent tables.
To declare a cascading relationship in the
the rules in the
$_referenceMap. Set the associative array keys
'onDelete' and 'onUpdate' to the string 'cascade'
(or the constant
self::CASCADE). Before a row is deleted from the
parent table, or its primary key values updated, any rows in the dependent table that
refer to the parent's row are deleted or updated first.
Example 335. Example Declaration of Cascading Operations
In the example below, rows in the Bugs table are automatically
deleted if the row in the Products table to which they refer is
deleted. The 'onDelete' element of the reference map entry is set
No cascading update is done in the example below if the primary key value in the
parent class is changed. The 'onUpdate' element of the reference
map entry is
self::RESTRICT. You can get the same result by
omitting the 'onUpdate' entry.
class BugsProducts extends Zend_Db_Table_Abstract
protected $_referenceMap = array(
'Product' => array(
'columns' => array('product_id'),
'refTableClass' => 'Products',
'refColumns' => array('product_id'),
'onDelete' => self::CASCADE,
'onUpdate' => self::RESTRICT
Cascading operations invoked by
This means that if your database implements and enforces referential integrity
constraints, a cascading
UPDATE executed by a
Zend_Db_Table class conflicts with the constraint, and
results in a referential integrity violation. You can use cascading
only if your database does not enforce that referential
DELETE suffers less from the problem of referential
integrity violations. You can delete dependent rows as a non-atomic action before
deleting the parent row that they reference.
However, for both
changing the database in a non-atomic way also creates the risk that another
database user can see the data in an inconsistent state. For example, if you delete
a row and all its dependent rows, there is a small chance that another database
client program can query the database after you have deleted the dependent rows, but
before you delete the parent row. That client program may see the parent row with no
dependent rows, and assume this is the intended state of the data. There is no way
for that client to know that its query read the database in the middle of a change.
The issue of non-atomic change can be mitigated by using transactions to isolate your change. But some RDBMS brands don't support transactions, or allow clients to read "dirty" changes that have not been committed yet.
Cascading operations in
Zend_Db_Table are invoked
Cascading deletes and updates defined in your
classes are applied if you execute the
delete() methods on the Row class. However, if you update
or delete data using another interface, such as a query tool or another application,
the cascading operations are not applied. Even when using
Zend_Db_Adapter class, cascading operations defined in
Zend_Db_Table classes are not executed.
There is no support for a cascading
INSERT. You must insert a
row to a parent table in one operation, and insert rows to a dependent table in a