sql - 更新数据库表

我有以下数据库表:

在这些表中，我具有以下元素:

容器:可以包含任何container_item元素；该关系使用表CONTAINER_CANDIDATES

存储

Container_Item:可以包含任何元素项；使用表COMPOUNDS

存储该关系

元素:系统中的基本元素。

让我用一个具体的案例来表达这个问题:

在表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'

在表CONTAINER_ITEM中，我可以存储以下元素:

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'

在表CONTAINER中，我可以存储以下元素:

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

使用表COMPOUNDS进行以下连接:

    - 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)

使用表CONTAINER_CANDIDATES建立以下连接:

        - 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)

因此，通过这种方式，我创建了所有连接。
现在的问题是，如何删除ContainerName01及其下的所有项(容器项和其下的元素)，以使其他Container(例如:ContainerName02)完全不受影响？

我想使用Oracle PL SQL过程来实现

最佳答案

好吧，如果您遵循良好的做法，这并不是一个很难解决的问题。

首先，您有两个“多对多”跳转表(CONTAINER_CANDIDATES和COMPOUNDS)，因为其中的孤立行将完全没有用，我们将在其上添加一个DELETE CASCADE。

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;

现在，事情几乎可以自己完成，这是一个很小的存储过程，可以确保我们不会保留未使用的CONTAINER_ITEM和ELEMENTS，我们很好。

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;
/

这不能保证您的任何 table 上都没有孤儿。它使用Cascade来完成大部分工作，并在两个从表中对未使用的数据进行少量修整。

唯一的缺点是，如果不使用它们，将不允许您保留CONTAINER_ITEM和ELEMENTS中的条目。

关于sql - 更新数据库表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42853283/