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.