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