How a probably NULL-valued attribute could still reference another attribute in MySQL

Imagine MySQL table descr开发者_JAVA技巧ibing a folders in filesystem:

folder_id INT,
parent_folder_id INT,
folder_name VARCHAR(64)

And I want to add a constraint 'parent_folder_id REFERENCES folder_id' to be sure that no dead links are there in the DB.

But in case folder is in the top-level there is no parent folder, so it should be NULL. As far as I understand, constraint won't let me insert tuple with parent_folder_id = NULL.

How to design it properly?

You can just add a foreign key:

CREATE TABLE `folder` (
    `folder_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_folder_id` INTEGER UNSIGNED,
    `folder_name ` VARCHAR(64) NOT NULL,
    CONSTRAINT `PK_folder` PRIMARY KEY (`folder_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `folder` ADD CONSTRAINT `FK_folder_parent_folder` 
    FOREIGN KEY (`parent_folder_id`) REFERENCES `folder` (`folder_id`) 
    ON DELETE CASCADE ON UPDATE CASCADE;

The foreign key constraint won't kick in when parent_folder_id is NULL.

I think you'd better using two triggers (one on insert and one on update) checking that parent_folder_id exists in your table in folder_id column; if not raise an error or discard the insert/update operation.
For root folder assign parent_folder_id = 0 and use a CASE statement in your trigger to make this valid.

EDITED: use this as pseudo-code

CREATE TRIGGER trig_ins BEFORE INSERT ON table
  FOR EACH ROW BEGIN
    IF (NEW.parent_folder_id > 0) THEN
      IF (SELECT COUNT(folder_id) FROM table 
          WHERE folder_id = NEW.parent_folder_id) = 0 THEN
              raise_an_error_here
      END
    END
  END

I should just add a record in the folder table, called "root", for which the parent_folder is himself. But I never tested it and do not know if the system will accept this.