DROP TABLE command: Changes to CASCADE behavior for hybrid tables (Preview)¶

When this behavior change bundle is enabled, the default setting of the CASCADE/RESTRICT parameter is different for hybrid tables. This change does not apply to standard tables.

Before the change:

When you drop a hybrid table without specifying RESTRICT or CASCADE, and the hybrid table has a primary-key/foreign-key or unique-key/foreign-key relationship with another table, the DROP TABLE command succeeds.

The default behavior is CASCADE.

CREATE OR REPLACE HYBRID TABLE ht1(
  col1 NUMBER(38,0) NOT NULL,
  col2 NUMBER(38,0) NOT NULL,
  CONSTRAINT pkey_ht1 PRIMARY KEY (col1, col2));

CREATE OR REPLACE HYBRID TABLE ht2(
  cola NUMBER(38,0) NOT NULL,
  colb NUMBER(38,0) NOT NULL,
  colc NUMBER(38,0) NOT NULL,
  CONSTRAINT pkey_ht2 PRIMARY KEY (cola),
  CONSTRAINT fkey_ht1 FOREIGN KEY (colb, colc) REFERENCES ht1(col1,col2));

DROP TABLE ht1;

Copy

The DROP TABLE command succeeds without any error.

After the change:

When you drop a hybrid table without specifying the RESTRICT or CASCADE option, and the hybrid table has a primary-key/foreign-key or unique-key/foreign-key relationship with another table, the DROP TABLE command fails with an error.

The default behavior is RESTRICT.

For example:

DROP TABLE ht1;

Copy

SQL compilation error:
Cannot drop the table because of dependencies

The DROP TABLE command fails in this case. If necessary, you can override the default behavior by specifying CASCADE in the DROP TABLE command.

DROP TABLE ht1 CASCADE;

Copy

Alternatively in this case, you could drop the dependent table ht2 first, then drop table ht1.

Ref: 1741