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" <ryvo@centrum.cz> wrote in message
news:f01u3f$fb6$1@ns.felk.cvut.cz...
> 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
>

On your delete statements you could have:

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

Sean

On Tue, 17 Apr 2007 09:15:05 +0100, "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

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.

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:1176797100.286636@kestrel.skynet.co.uk...
>
> "Vojta" <ryvo@centrum.cz> wrote in message
> news:f01u3f$fb6$1@ns.felk.cvut.cz...
>> 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
>>
>
> On your delete statements you could have:
>
> DELETE FROM ORGANISATIONS
> WHERE ID NOT IN
> (
> SELECT PARENTID
> FROM ORGANISATIONS
> )
> AND ID = n
>
> Sean
>
>
>
>

On 17 Apr, 09:15, "Sean" <sean.anderson@[nospam]oakleafgroup.biz>
wrote:
> "Vojta" <r...@centrum.cz> wrote in message
>
> news:f01u3f$fb6$1@ns.felk.cvut.cz...
>
>
>
>
>
> > 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
>
> 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 -

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.

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 parsed.

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" <zzsubtenantezz@gmail.com> píse v diskusním príspevku
news:231923l862pjr07iuu2r6i9melda4dgfar@4ax.com...
> On Tue, 17 Apr 2007 09:15:05 +0100, "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
>
> 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.

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


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

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.

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" <zzsubtenantezz@gmail.com> píse v diskusním príspevku
news:lq5923tc7m2o6o5vsoin0kb47bp4giart3@4ax.com...
> On Tue, 17 Apr 2007 11:14:11 +0200, "Vojta" <ryvo@centrum.cz> wrote:
>
>
>>ALTER TABLE MemberTree ADD CONSTRAINT C_MemberTree_1
>>FOREIGN KEY (ParentId) REFERENCES MemberTree(Id) ON DELETE CASCADE; (*)
>
> 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.

Vojta wrote:
> 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 parsed.
>
> 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" <zzsubtenantezz@gmail.com> píse v diskusním príspevku
> news:231923l862pjr07iuu2r6i9melda4dgfar@4ax.com...
>> On Tue, 17 Apr 2007 09:15:05 +0100, "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
>> 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.
>
>

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.
jstucklex@attglobal.net
==================