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.