1. Cache Guide
  2. Defining Cache Groups
  3. ON DELETE CASCADE Cache Table Attribute

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 the ON DELETE CASCADE cache table attribute must be a proper subset of the foreign keys in the cached Oracle Database tables that use the ON 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.