ON DELETE CASCADE Cache Table Attribute
The ON DELETE CASCADE
cache table attribute can be specified for cache
tables in any cache group type.
ON DELETE CASCADE
specifies that when rows containing referenced
key values are deleted from a parent table, rows in child tables with dependent foreign keys are
also deleted.
The following example demonstrates how to use the ON DELETE CASCADE
cache table attribute on the child table's foreign key definition:
CREATE READONLY CACHE GROUP customer_orders FROM sales.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)), sales.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES sales.customer(cust_num) ON DELETE CASCADE);
All paths from a parent table to a child table must be either "delete" paths or "do not delete" paths. There cannot be some "delete" paths and some "do not delete" paths from a parent table to a child table. Specify the ON DELETE CASCADE
cache table attribute for child tables on a "delete" path.
The following restrictions apply when using the ON DELETE CASCADE
cache table attribute:
-
For AWT and SWT cache groups, and for TimesTen cache tables in user managed cache groups that use the
PROPAGATE
cache table attribute, foreign keys in cache tables that use theON DELETE CASCADE
cache table attribute must be a proper subset of the foreign keys in the cached Oracle Database tables that use theON DELETE CASCADE
attribute.ON DELETE CASCADE
actions on the cached Oracle Database tables are applied to the cache tables on TimesTen as individual deletes.ON DELETE CASCADE
actions on the cache tables are applied to the cached Oracle Database tables as a cascaded operation. -
Matching of foreign keys between the cache tables on TimesTen and the cached Oracle Database tables is enforced only when the cache group is being created. A cascade delete operation may not work if the foreign keys on the cached Oracle Database tables are altered after the cache group is created.
See CREATE CACHE GROUP in the Oracle TimesTen In-Memory Database SQL Reference.