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