1

I'm trying to model a directory tree in MySQL, my first idea was:

CREATE TABLE Folder (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR (255) NOT NULL,
    parentId INT UNSIGNED,

    UNIQUE INDEX (name, parentId),
    CONSTRAINT FOREIGN KEY (parentId) REFERENCES Folder (id)

)

But due to the fact that MySQL allows multiple NULLs in a unique index, with this definition I could have 2 folders with the same name at root level like so:

id  | name  | parentId
----------------------
1   | a     | null
2   | a     | null

Can you find a way to have both of the following?

  1. a foreign key preventing a folder from being the child of a non-existing folder
  2. a uniqueness constraint preventing a folder from containing two files with the same name
djfm
  • 2,317
  • 1
  • 18
  • 34

1 Answers1

0

MySQL does not support check constraints. One possible way to enforce such advanced conditions is to use a before insert/update trigger, and throw an error if the insert/update doesn't satisfy your conditions.

Community
  • 1
  • 1
Franck Dernoncourt
  • 77,520
  • 72
  • 342
  • 501