I'm working on a forum system and want an easy way to delete forum groups/forums and posts.

Basically: The system works like this:

Forum Group 1
-- Forum 1
--- Post1
-- Forum 2
Forum Group 2
-- Forum 1
--- Post 1
--- Post 2
-- Forum 2

So you create a forum group, and then create forums. And in each forum you can post.

I want to be able to delete a forum OR forum group (both held in the same table) and it will automatically delete all related posts.

Here's the SQL so far:

The parent_id relates to the forum_id, so I can tell the difference between forum groups and forums.

CREATE TABLE forum (
  forum_id int(11) auto_increment,
  topic varchar(100) NOT NULL default '',
  description longtext NOT NULL,
  removeable int(1) NOT NULL default 1,
  group_order int(10) NOT NULL default 0,
  forum_order int(10) NOT NULL default 0,
  parent_id int(10) NOT NULL default 0,
  PRIMARY KEY  (forum_id),
  KEY id (parent_id) REFERENCES forum(forum_id) ON DELETE CASCADE
) ;

CREATE TABLE post (
  post_id int(11) auto_increment,
  parent_id int(11) default 0,
  forum_id int(11) NOT NULL,
  topic varchar(30) default NULL,
  content longtext NOT NULL,
  user varchar(20) NOT NULL,
  ip varchar(15) NOT NULL,
  datetime datetime default NULL,
  replytime datetime default NULL,
  stick int(1) default 0,
  closed VARCHAR(100) NULL DEFAULT '',
  PRIMARY KEY  (post_id),
  KEY id (post_id),
  FOREIGN KEY (forum_id) REFERENCES forum(forum_id) ON DELETE CASCADE
) ;

The cascade references don't work for some reason.

For example, my delete query is this:

DELETE FROM forum WHERE forum_id = $forum_id;

Shouldn't this delete posts in the post table where forum_id = $forum_id ?

If you understand what I'm rambling on about, I'd appreciate any help.

Thanks.

    Did you make sure to declare them as innodb tables? You have to do that for MySQL to actually use fks. Note that it fails silently if you forget to declare the tables as innodb type.

      Didn't make a difference, here's the updated tables:

      CREATE TABLE forum (
  forum_id int(11) auto_increment,
  topic varchar(100) NOT NULL default '',
  description longtext NOT NULL,
  removeable int(1) NOT NULL default 1,
  group_order int(10) NOT NULL default 0,
  forum_order int(10) NOT NULL default 0,
  parent_id int(10) NOT NULL default 0,
  PRIMARY KEY  (forum_id),
  KEY id (parent_id),
  FOREIGN KEY (parent_id) REFERENCES forum(forum_id) ON DELETE CASCADE
) TYPE=INNODB;


CREATE TABLE post (
  post_id int(11) auto_increment,
  parent_id int(11) default 0,
  forum_id int(11) NOT NULL,
  topic varchar(30) default NULL,
  content longtext NOT NULL,
  user varchar(20) NOT NULL,
  ip varchar(15) NOT NULL,
  datetime datetime default NULL,
  replytime datetime default NULL,
  stick int(1) default 0,
  closed VARCHAR(100) NULL DEFAULT '',
  PRIMARY KEY  (post_id),
  KEY id (post_id),
  FOREIGN KEY (forum_id) REFERENCES forum(forum_id) ON DELETE CASCADE
) TYPE=INNODB;

        Are you sure your version of MySQL supports innodb types? The ones that don't just silently fail when you try to declare the tables as innodb.

          Write a Reply...