Foreign key constraint (complex?)

Question

I have a table that manages virtual download folders for a site.

CREATE TABLE `folder` (
  # id = PK
  `id` int(10) unsigned NOT NULL auto_increment,
  # folderId = self referencing FK to id
  `folderId` int(10) unsigned default NULL,
  # siteId = FK to id in site table
  `siteId` int(10) unsigned NOT NULL
)

Ideally I like siteId to reference id in table site, but also have a compound FK folderId, siteId to be self referencing to id, siteId in table folder.

The reason I want this is that I want to guarantee that a child folder always belongs to the same site as the parent folder.

Is something like this possible? If so, how? And if not, do you have any other suggestions how I can guarantee this state?

EDIT:
Sorry, I should have mentioned this earlier: I know about InnoDB, and as a matter of fact, I'm using it already (along with foreign keys). But the question is, how will I be able to guarantee these two things:

• A record in folder must reference a valid site with siteId
• A record in folder must also be the same siteId as the parent record (only if it is a child of course)

I tried this so far:

  # this one should guarantee folder belongs to
  # a valid site (this one works of course)
  CONSTRAINT `FK_folder_site_1`
    FOREIGN KEY (`siteId`)
    REFERENCES `site` (`id`) ON UPDATE CASCADE,

  # this one should guarantee child folder belongs to
  # the same site as parent folder (doesn't work)
  CONSTRAINT `FK_folder_folder_1`
    FOREIGN KEY `FK_folder_folder_1` (`folderId`, `siteId`)
    REFERENCES `folder` (`id`, `siteId`) ON DELETE CASCADE ON UPDATE CASCADE

So the last one doesn't work (of course?) because it seems logical that a foreign key (siteId) can't reference two different fields. What can you suggest to make my proposed constraints work?

Answer 1

The InnoDB storage engine supports self-referencing foreign keys, and you can use them to emulate a hierarchical structure. To declare the key, you could do something like this in your CREATE TABLE statement:

FOREIGN KEY (`folderId`,`siteId`) REFERENCES `folder` (`Id`, `siteId`)

An important thing to remember is that this key should be NULL-able, so you can indicate which record(s) are the root of the tree.

Answer 2

You can do this if you use the InnoDB storage engine

CREATE TABLE `folder` (
  # id = PK
  `id` int(10) unsigned NOT NULL auto_increment,
  # folderId = self referencing FK to id
  `folderId` int(10) unsigned default NULL,
  # siteId = FK to id in site table
  `siteId` int(10) unsigned NOT NULL,
  FOREIGN KEY (folder_id, site_id) REFERENCES folder(id, site_id)
) ENGINE=INNODB;

It didn't test it but it should work. See FOREIGN KEY Constraints.

If you want/have to use MyISAM, of course you can do this logically the same way as you do it with the "normal" foreign key constraints.