Cleaning Data Bound by Foreign Key Constraints

Integrity constraints are put in place to ensure that the data insertion/updation and deletion are bound within the applied business logic. However there are situations where we might need to remove the data from certain tables but it is not allowed by the database system due to constraints imposed while creating/updating the database tables.

One of the constraints normally imposed between the tables is foreign key constraint where primary key data of one of the tables is used as a key record in the child table. Therefore in such cases the child records needs to the deleted before parent record. This ensures that there are no orphan records. In automated scenarios where data is generated on nightly/hourly basis it becomes the imperative the records be cleaned periodically to keep the volume in control.

If the system under test is external and has not been not designed for cleanup activities then the you might need to alter the tables/constraints to change the behavior as per the requirement. Oracle system provides three modes in which the constraints can be in:

• ON DELETE CASCADE

• ON DELETE SET NULL

• ON DELETE NO ACTION

On using the ‘ON DELETE CASCADE’ option while setting or altering the constraint, only the parent record needs to be explicitly removed. The child records are automatically removed when this option is used.

On using the ‘ON DELETE SET NULL’ option the deletion of the parent records will be allowed but the child records are set to null.

On using the ‘ON DELETE NO ACTION’ option the deletion of parent records is prevented until the child records are present. This is the default mode if either of the above two states are not specified.

An example of creating the tables with ‘ON DELETE CASCADE’ option is as follows:

CREATE TABLE root

( root_id numeric(10) >not null,

  root_name varchar2(50) not null,

  contact_name varchar2(50),

  CONSTRAINT root_pk PRIMARY KEY (root_id)

);

CREATE TABLE branch

( branch_id numeric(10) not null,

  root_id numeric(10) not null,

  CONSTRAINT fk_root

    FOREIGN KEY (root_id)

    REFERENCES root(root_id)

    ON DELETE CASCADE

);

If the table already exists try to alter the constraints using the following:

ALTER TABLE <CHILD_TABLE_NAME> MODIFY CONSTRAINT <foreign_key_name> foreign key(ref_child_columnname) references parent_tablename(ref_parent_columnname) ON DELETE CASCADE;