Jailer - Deleting Data

This tutorial shows you how to delete rows from database without violating integrity of the remaining data.

Deleting data is closely coupled with data exportation. The same restrictions are applied, no row will be deleted which is not also exported. This way you can always restore deleted rows by re-importing them.

A delete-script contains DELETE statements for all exported rows which are not associated with any non-exported row according to the restricted data model. Additionally tables can be excluded from deletion so that their rows will never be deleted (see step 2)

Objective

Delete all projects which have ended before 2008-01-01. Delete all associated participations too but no role and no employee.

Step 1. Delete projects

Create a new model. Select table PROJECT as subject table and enter T.END_DATE<'2008-01-01' as subject condition.

In order to prevent the deletion of a PROJECT_PARTICIPATION, which is not associated with an old project but with a role of a participation in an old project, disable the association from ROLE to PROJECT_PARTICIPATION. Do the same with the association from EMPLOYEE to PROJECT_PARTICIPATION.

Disable all associations to EMPLOYEE too. This will reduce the set of exported employee-rows to those employees who have participated in an old project.

Open the data export dialog. Enter delete-old-projects.sql as name for the delete-script and start the exportation.

Jailer has generated a script for the deletion of the two old projects and the six associated participations, but also for the deletion of six employees and one role. This role is the role of an participation in an old project, but not of a remaining one. The six employees have participated in an old project but in none of the remaining projects.

Step 2. Prevent employees and roles from being deleted

Open the data model editor (Tools->Data Model Editor). Edit the table EMPLOYEE and switch on the check-box "exclude from deletion". Do the same with table ROLE.
(Since version 6.7 it's also possible to do this via the table's context menu)

Export Data again. Employees and roles are now excluded from deletion:

delete-old-projects.sql

-- generated by Jailer at Wed Fri May 02 12:37:07 CEST 2016 from wisser@desktop
--
-- extraction model: EMPLOYEE where NAME='SCOTT' (extractionmodel/scott.csv)
-- database URL:      jdbc:db2://localhost/wisser
-- database user:     scott
-- Exported Entities: 25
--     DEPARTMENT                     2
--     EMPLOYEE                       8
--     PROJECT                        2
--     PROJECT_PARTICIPATION          6
--     ROLE                           3
--     SALARYGRADE                    4
--
-- Tabu-tables: { EMPLOYEE, ROLE }
--
-- Deleted Entities: 8
--      DEPARTMENT                    0 (-2)
--      PROJECT                       2
--      PROJECT_PARTICIPATION         6
--      SALARYGRADE                   0 (-4)

Delete from PROJECT_PARTICIPATION Where (PROJECTNO, EMPNO, START_DATE) in (values (1001, 7369, '2006-01-01'), (1001, 7788, '2006-05-15'), (1001, 7902, '2006-01-01'), (1002, 7782, '2006-08-22'), (1002, 7876, '2006-08-22'), (1002, 7934, '2007-01-01'));
Delete from PROJECT Where PROJECTNO in (1001, 1002);