html
css
c
mysql
linux
xcode
android
regex
visual-studio
multithreading
eclipse
json
perl
oracle
tsql
mvc
asp
api
jsp
postgresql
There is no such thing as a "conditional" constraint: Either your parent field allways references another row (i.e. no 0 allowed), or it is no constraint at all.
This is quite a normal usecase, typically you would work around it with an ON DELETE TRIGGER on the parent, that also deletes the children, selecting them by parent.
ON DELETE TRIGGER
In your example you might consider something like
CREATE TRIGGER cascade_delete_children BEFORE DELETE ON node FOR EACH ROW DELETE FROM node WHERE parent=OLD.id;
You can use NULL instead of 0 and define the FOREIGN KEY with cascading deletes:
NULL
0
FOREIGN KEY
parent INT UNSIGNED NULL DEFAULT NULL, --- here is the parent term id --- if it's NULL the term has no parent ... CONSTRAINT parent_fk FOREIGN KEY(parent) REFERENCES thisTable(id) ON DELETE CASCADE ON UPDATE RESTRICT