"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