1 /* Create a new table and delete previous table of the same name if it exists*/
 2 CREATE DATABASE IF NOT EXISTS `database`;
 3 
 4 DROP TABLE IF EXISTS `comments`;
 5 
 6 CREATE TABLE `comments` (
 7   `id` smallint(5) unsigned NOT NULL auto_increment,
 8   `parent_id` smallint(5) unsigned,
 9   `message` text NOT NULL default '',
10   `category` smallint(5) unsigned NOT NULL,
11   PRIMARY KEY (`id`),
12   INDEX (`category`),
13   FOREIGN KEY (`parent_id`) REFERENCES `comments`(`id`) ON DELETE CASCADE
14 ) TYPE=INNODB COMMENT='List of comments';
15 
16 
17 /* Populate with some data*/
18 
19 /* Try deleting a parent of a nested category and all its subchildren will be automatically deleted*/
20 
21 INSERT INTO comments VALUES (1,NULL,"Text test category 1", 1),
22 (2,NULL,"Text test category 2", 2),
23 (3,NULL,"Text test category 3", 3),
24 (4,NULL,"Text test category 4", 4);
25 INSERT INTO comments VALUES (5,1,"Text test category 1.1", 1),
26 (6,1,"Text test category 1.2", 1),
27 (7,1,"Text test category 1.3", 1),
28 (8,1,"Text test category 1.4", 1);
29 INSERT INTO comments VALUES (9,5,"Text test category 1.1.1", 1),
30 (10,5,"Text test category 1.1.2", 1),
31 (11,5,"Text test category 1.1.3", 1),
32 (12,5,"Text test category 1.1.4", 1);
33 INSERT INTO comments VALUES (13,6,"Text test category 1.2.1", 1),
34 (14,6,"Text test category 1.2.2", 1),
35 (15,6,"Text test category 1.2.3", 1),
36 (16,6,"Text test category 1.2.4", 1);