MySQL foreign keys recursive

Question

I have a "categories" table that has a structure:

• categoryId
• parentId
• categoryName

This table represents categorized tree structure where parentId is actually a pointer to categoryId.

Is it possible to put a foreign key RESTRICT to parentId which points to categoryId so that I disable deletion of children nodes?

Answer 1

Yes, certainly a table may have a foreign key referencing itself. eg.:

CREATE TABLE categories (
    categoryId INTEGER NOT NULL PRIMARY KEY,
    parentId INTEGER REFERENCES categories(categoryId),
    categoryName VARCHAR(32) NOT NULL
);

Naturally you will need to be using InnoDB not MyISAM for this to do anything.