CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldevelopertruncate
Djangu
Djangu

Reputation: 35

TRUNCATE on a parent table causes deletion of a child table records with NULL value for foreign key

Why are all rows deleted in a child table when using TRUNCATE with CASCADE clause on a parent table, even when some records in a child table have NULL value for the foreign key.

As far as I understand, setting a NULL value for a foreign key should not link the data in the given row to a parent table.

The statements that this was tested with, in Oracle 12c and 18 (Live SQL), are as follows:

CREATE TABLE ports 
  ( 
     port_id   NUMBER, 
     port_name VARCHAR2(20) CONSTRAINT port_description_nn NOT NULL ENABLE, 
     country   VARCHAR2(40), 
     capacity  NUMBER, 
     CONSTRAINT port_pk PRIMARY KEY (port_id) 
  ); 

CREATE TABLE ships 
  ( 
     ship_id      NUMBER PRIMARY KEY, 
     ship_name    VARCHAR2(20), 
     home_port_id NUMBER, 
     CONSTRAINT ships_ports_fk FOREIGN KEY (home_port_id) REFERENCES ports ( 
     port_id) ON DELETE CASCADE 
  ); 

INSERT INTO ports 
VALUES      (315, 
             'ATLANTA', 
             'USA', 
             100000); 

INSERT INTO ships 
VALUES      (4000, 
             'CODD LAND ROVER', 
             315); 

INSERT INTO ships 
VALUES      (4001, 
             'CODD VESSEL TWO', 
             NULL);

After this, the following statement

TRUNCATE TABLE PORTS CASCADE;

deletes all rows in SHIPS table, even though the 'Codd Vessel Two' has NULL value for the HOME_PORT_ID, which is a foreign key.

Is this an expected result, due to the ON DELETE CASCADE clause when the foreign key was created?

Upvotes: 2

Views: 1845

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Is this an expected result?

Yes. The documentation of TRUNCATE command says that:

CASCADE

If you specify CASCADE, then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.

Upvotes: 2

Related Questions