CODEWITHSUNDEEP
sqloracle-databaseplsql
Lucian
Lucian

Reputation: 354

Update database tables

I have the following database tables : enter image description here

In these tables I have the following elements :

Let me phrase the question using a concrete case:

In the table ELEMENTS I can store the following elements:

Id = 1 ; ElementName = 'element001'
Id = 2 ; ElementName = 'element002'
Id = 3 ; ElementName = 'element003'
Id = 4 ; ElementName = 'element004'
Id = 5 ; ElementName = 'element005'
Id = 6 ; ElementName = 'element006'
Id = 7 ; ElementName = 'element007'

In table CONTAINER_ITEM I can store the following elements:

Id = 1 ; ContainerItemName = 'item-id-aaa'
Id = 2 ; ContainerItemName = 'item-id-bbb'
Id = 3 ; ContainerItemName = 'item-id-ccc'
Id = 4 ; ContainerItemName = 'item-id-ddd'
Id = 5 ; ContainerItemName = 'item-id-eee'

In table CONTAINER, I can store the following elements:

Id = 1; ContainerName = 'ContainerName01';
Id = 2; ContainerName = 'ContainerName02';

Using the table COMPOUNDS I make the following connections:

    - item-id-aaa  (id = 1 in Container_Item table)
        -> element001 (id = 1 in Elements table)
        -> element002 (id = 2 in Elements table)
    - item-id-bbb (id = 2 in Container_Item table)
        -> element003 (id = 3 in Elements table)
        -> element004 (id = 4 in Elements table)
    - item-id-ccc (id = 3 in Container_Item table)
        -> element005 (id = 5 in Elements table)
        -> element006 (id = 6 in Elements table)
    - item-id-ddd (id = 4 in Container_Item table)
        -> element005 (id = 5 in Elements table)
        -> element007 (id = 7 in Elemens table);
    - item-id-eee (id = 5 in Container_Item table)
        -> element-007 (id = 7 in Elemens table)

Using the table CONTAINER_CANDIDATES I make the following connections:

        - ContainerName01 contains the following :
            -> item-id-aaa (id = 1 in Container_Item table)
            -> item-id-bbb (id = 2 in COntainer_Item table)
            -> item-id-ccc (id = 3 in COntainer_Item table)
            -> item-id-ddd (id = 4 in COntainer_Item table)
        - ContainerName02 contains the following:
            -> item-id-aaa (id = 1 in Container_Item table)
            -> item-id-eee (id = 5 in COntainer_Item table)

So in this way I created all my connections. Now the question is how can I delete the ContainerName01 and all the items under it (Container Items and Elements under it) so that other Containers (example : ContainerName02) is not affected at all ?

I want to achieve this using an Oracle PL SQL procedure

Upvotes: 6

Views: 418

Answers (2)

Blag
Blag

Reputation: 5894

Ok this is not a really hard problem if you follow good practices.

First, you have two "many-to-many" jump tables (CONTAINER_CANDIDATES & COMPOUNDS), as orphan row in these will be totally useless, we'll add a DELETE CASCADE on them.

ALTER TABLE CONTAINER_CANDIDATES
ADD CONSTRAINT FK_CC_CONTAINER
   FOREIGN KEY (CONTAINERID)
   REFERENCES CONTAINER (ID)
   ON DELETE CASCADE;

ALTER TABLE CONTAINER_CANDIDATES
ADD CONSTRAINT FK_CC_CONTAINER_ITEM
   FOREIGN KEY (CONTAINERITEMID)
   REFERENCES CONTAINER_ITEM (ID)
   ON DELETE CASCADE;

ALTER TABLE COMPOUNDS
ADD CONSTRAINT FK_COMPOUNDS_CONTAINER_ITEM
   FOREIGN KEY (CONTAINERITEMID)
   REFERENCES CONTAINER_ITEM (ID)
   ON DELETE CASCADE;

ALTER TABLE COMPOUNDS
ADD CONSTRAINT FK_COMPOUNDS_ELEMENTS
   FOREIGN KEY (ELEMENTSID)
   REFERENCES ELEMENTS (ID)
   ON DELETE CASCADE;

Now, things nearly work by themselves, a small stored procedure to ensure we will not keep unused CONTAINER_ITEM and ELEMENTS and we are good.

CREATE OR REPLACE PROCEDURE cascaded_delete_container (
    P_CONTAINER_ID VARCHAR2
) IS
BEGIN
        -- remove the master from supplied ID
        -- cascade on CONTAINER_CANDIDATES
    DELETE FROM CONTAINER
    WHERE ID = P_CONTAINER_ID;

        -- remove CONTAINER_ITEM not used in CONTAINER_CANDIDATES
        -- cascade on COMPOUNDS
    DELETE FROM CONTAINER_ITEM
    WHERE NOT EXISTS(
        SELECT 1 
        FROM CONTAINER_CANDIDATES 
        WHERE CONTAINER_ITEM.ID = CONTAINER_CANDIDATES.CONTAINERITEMID
        );

        -- remove ELEMENTS not used in COMPOUNDS
    DELETE FROM ELEMENTS
    WHERE NOT EXISTS(
        SELECT 1 
        FROM COMPOUNDS 
        WHERE ELEMENTS.ID = COMPOUNDS.ELEMENTSID
        );

    COMMIT;

END;
/

This unsure you never have orphan in any of your table. It use the Cascade to do most of the work and just do a small trim of unused data in the two slave tables.

The only downside is this will not allow you to keep entry in CONTAINER_ITEM and ELEMENTS if you don't use them.

Upvotes: 2

Aurimas V
Aurimas V

Reputation: 1

If you want to achieve this via a procedure the way to go would be to pass the container ID as a parameter and then delete the child entries that you fetched via cursor. As I understood, you need to delete the entries in CONTAINER_CANDIDATES and COMPOUNDS tables first. For example:

create or replace procedure delete_container(p_container_id number) is

  -- Get all compound child etries via container ID
  cursor c_get_compounds(cp_container_id number) is
    select comp.id
      from compounds comp, container_candidates cc
     where comp.containerItemID = cc.containerItemID
       and cc.containerID = cp_container_id;

  -- Get all container candidate child entries via container ID
  cursor c_get_container_candidates(cp_container_id number) is
    select cc.id
      from container_candidates cc
     where cc.containerID = cp_container_id;

begin

  -- Fetch all compound entries
  for r in c_get_compounds(cp_container_id => p_container_id) loop
    -- Delete compound entries
    delete from compounds where id = r.id;
  end loop;

  -- Fetch all container candidates
  for r in c_get_container_candidates(cp_container_id => p_container_id) loop
    -- Delete container candidates
    delete from container_candidates where id = r.id;
  end loop;

  -- Delete container entry
  delete from container where id = p_container_id;
end delete_container;

Check if ID's are correct in the code

Upvotes: -1

Related Questions