Professional Web Applications Themes

Organisation tree - prevent deletion of parent organisation - MySQL

Please, can you advise? I store organisation tree in single table and I need to prevent deletion of organisation that has child organisations. I store organisation structure in following table: CREATE TABLE Organisations ( Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ParentId INT UNSIGNED, Name VARCHAR(50) ) ParentId points to parent organisation. If it is NULL then the organisation is root organisation. Otherwise it is child of parent identified by ParentId. I need to prevent deletion of any organisation that has children organisations. It means if there exists at least one record whose ParentId equals to Id of record to delete, ...

  1. #1

    Default Organisation tree - prevent deletion of parent organisation

    Please, can you advise? I store organisation tree in single table and I need
    to prevent deletion of organisation that has child organisations. I store
    organisation structure in following table:

    CREATE TABLE Organisations (
    Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ParentId INT UNSIGNED,
    Name VARCHAR(50)
    )

    ParentId points to parent organisation. If it is NULL then the organisation
    is root organisation. Otherwise it is child of parent identified by
    ParentId.

    I need to prevent deletion of any organisation that has children
    organisations. It means if there exists at least one record whose ParentId
    equals to Id of record to delete, deletion should not be executed. Is it
    good idea to use self referencing foreign key (reference within single
    table) and how to do it? Or is there another better way?

    Thanks a lot! Vojta


    Vojta Guest

  2. #2

    Default Re: Organisation tree - prevent deletion of parent organisation


    "Vojta" <cz> wrote in message
    news:f01u3f$fb6$felk.cvut.cz... 

    On your delete statements you could have:

    DELETE FROM ORGANISATIONS
    WHERE ID NOT IN
    (
    SELECT PARENTID
    FROM ORGANISATIONS
    )
    AND ID = n

    Sean




    Sean Guest

  3. #3

    Default Re: Organisation tree - prevent deletion of parent organisation

    On Tue, 17 Apr 2007 09:15:05 +0100, "Sean"
    <sean.anderson[nospam]oakleafgroup.biz> wrote:
     

    Well, adding a FOREIGN KEY constraint on parentid (foreign key on the
    id primary key column), might work better and without having to do
    tricks in the delete statements : if you try to delete a row with an
    id used as parentid somewhere, you will have a constraint failure and
    the delete will not proceed.
    subtenante Guest

  4. #4

    Default Re: Organisation tree - prevent deletion of parent organisation

    Thank you Sean! But if somebody will forget to make deletion conditional, it
    will break my organisation tree. So I need to ensure, that such records
    cannot be deleted.


    "Sean" <sean.anderson[nospam]oakleafgroup.biz> píše v diskusním příspěvku
    news:skynet.co.uk... 
    >
    > On your delete statements you could have:
    >
    > DELETE FROM ORGANISATIONS
    > WHERE ID NOT IN
    > (
    > SELECT PARENTID
    > FROM ORGANISATIONS
    > )
    > AND ID = n
    >
    > Sean
    >
    >
    >
    >[/ref]


    Vojta Guest

  5. #5

    Default Re: Organisation tree - prevent deletion of parent organisation

    On 17 Apr, 09:15, "Sean" <sean.anderson[nospam]oakleafgroup.biz>
    wrote: 




    >
    > On your delete statements you could have:
    >
    > DELETE FROM ORGANISATIONS
    > WHERE ID NOT IN
    > (
    > SELECT PARENTID
    > FROM ORGANISATIONS
    > )
    > AND ID = n
    >
    > Sean- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    You should do this as a LEFT JOIN and not a sub-select. It will be
    much more efficient.

    However, the answer to the OP's problem is to use Foreign Key
    Constraints.

    Captain Guest

  6. #6

    Default Re: Organisation tree - prevent deletion of parent organisation

    Thank you all for your answers!

    I tested foreign key:

    CREATE TABLE MemberTree (
    Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ParentId INT UNSIGNED ZEROFILL,
    Name VARCHAR(50) NOT NULL UNIQUE,
    CONSTRAINT PK_MemberTree_1 PRIMARY KEY (Id)
    ) ENGINE = InnoDB;

    ALTER TABLE MemberTree ADD CONSTRAINT C_MemberTree_1
    FOREIGN KEY (ParentId) REFERENCES MemberTree(Id) ON DELETE CASCADE; (*)

    (*) I wanted to use CHECK to prevent deletion if there are some children but
    I learned CHECK statement is ignored though pd.

    Now the test:

    INSERT INTO MemberTree VALUES(NULL, NULL, 'Member A');
    INSERT INTO MemberTree VALUES(NULL, 1, 'Member A.A');
    SELECT * FROM MemberTree;

    Id ParentId Name
    --------------------------------
    1 null 'Member A'
    2 1 'Member A.A'

    DELETE FROM MemberTree WHERE Id = 1;
    SELECT * FROM MemberTree;

    All records deleted.

    So it does not work, may be it should be done somehow else. Can you please
    advice how?




    "subtenante" <com> píse v diskusním príspevku
    news:com... 
    >
    > Well, adding a FOREIGN KEY constraint on parentid (foreign key on the
    > id primary key column), might work better and without having to do
    > tricks in the delete statements : if you try to delete a row with an
    > id used as parentid somewhere, you will have a constraint failure and
    > the delete will not proceed.[/ref]


    Vojta Guest

  7. #7

    Default Re: Organisation tree - prevent deletion of parent organisation

    On Tue, 17 Apr 2007 11:14:11 +0200, "Vojta" <cz> wrote:

     

    Yes.
    The problem is in your ON DELETE CASCADE.
    This means that if you delete a row, all its children will be erased
    too.
    So simply take it out of the definition, it should work fine.
    subtenante Guest

  8. #8

    Default Re: Organisation tree - prevent deletion of parent organisation

    That's great! Now it works! If I replace ON DELETE CASCADE with ON DELETE
    RESTRICT or I completely omnit it, it does exactly what I needed!

    CREATE TABLE Organisations (
    Id INT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ParentId INT UNSIGNED ZEROFILL,
    Name VARCHAR(50) NOT NULL UNIQUE
    ) ENGINE = InnoDB;
    ALTER TABLE Organisations ADD CONSTRAINT C_Organisations_ParentId
    FOREIGN KEY (ParentId)
    REFERENCES Organisations(Id);

    Thank you a lot all!

    "subtenante" <com> píse v diskusním príspevku
    news:com... 
    >
    > Yes.
    > The problem is in your ON DELETE CASCADE.
    > This means that if you delete a row, all its children will be erased
    > too.
    > So simply take it out of the definition, it should work fine.[/ref]


    Vojta Guest

  9. #9

    Default Re: Organisation tree - prevent deletion of parent organisation

    Vojta wrote: 
    >> Well, adding a FOREIGN KEY constraint on parentid (foreign key on the
    >> id primary key column), might work better and without having to do
    >> tricks in the delete statements : if you try to delete a row with an
    >> id used as parentid somewhere, you will have a constraint failure and
    >> the delete will not proceed.[/ref]
    >
    >[/ref]

    ALTER TABLE MemberTree ADD CONSTRAINT C_MemberTree_1
    FOREIGN KEY (ParentId) REFERENCES MemberTree(Id) ON DELETE CASCADE;
    ^^^^^^^^^^^^^^^^^^
    ON DELETE RESTRICT;

    It's working perfectly - doing exactly what you told it to do.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. How to -- folder organisation
    By Atomic in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: November 26th, 08:17 PM
  2. Help with Folder Organisation...
    By Atomic in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: November 13th, 09:31 PM
  3. Folder organisation help...
    By Atomic in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: November 12th, 04:54 AM
  4. Folder organisation...
    By Atomic in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: October 29th, 09:52 AM
  5. Organisation Chart
    By vasanth kumar in forum ASP Components
    Replies: 1
    Last Post: January 3rd, 02:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139