Maintain column relationships in MySQL

Question

I have a table called modules and I want to enforce the following logic, but I am not sure the best way to do it. I was thinking adding an update and insert trigger, but having issues with the trigger code.

If the module_sub_id greater than ZERO, then check to see if the module_sub_id exists as a module_id. If ZERO then consider the row a parent record.

The idea that I am trying to do is have a parent record which can have children records, without having another table with a foreign key relationship between them.

CREATE TABLE `jlimited_test2`.`modules` (
   `module_id` int( 11 ) NOT NULL AUTO_INCREMENT
   , `module_sub_id` int( 11 ) NOT NULL default '0'
   , `module_name` varchar( 100 ) CHARACTER SET utf8 NOT NULL
   , `module_active` int( 11 ) NOT NULL
   , PRIMARY KEY ( `module_id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

Here is the code for my trigger.

CREATE TRIGGER myTrigger
BEFORE INSERT ON modules
FOR EACH ROW
BEGIN
   IF NEW.module_sub_id > 0 THEN
      IF NOT (
              SELECT count(*)
              FROM modules
              WHERE module_id = NEW.module_sub_id
             ) = 1 THEN
         INSERT ignore()
      END IF;
   END IF;
END$$

Not a big deal - but can you format your code with some line breaks to make it more readable. — Adrian Cornish, Jan 14 '12 at 04:00
Why are you deliberately making things hard for yourself? If it's a 1:N one-way relationship, either make a new column referencing the "parent" column, or just create a new table and work with that. You're deliberately making things hard for yourself for no foreseeable reason. — jmkeyes, Jan 14 '12 at 04:03
Is the hierarchy supposed to have a maximum depth of 2, or can children themselves have children? — outis, Jan 14 '12 at 10:35
Prefixing `module_` on all [column names](http://stackoverflow.com/questions/1318272/) is redundant. — outis, Jan 14 '12 at 10:39
@JoshuaK: I believe that `module_sub_id` is supposed to be the parent (as a value of "0" indicates the row has no parent), it's just poorly named. — outis, Jan 17 '12 at 17:58

outis · Answer 1 · 2012-01-14T10:43:47.620

Easier would be to make module_sub_id a foreign key referencing module_id, and use NULL rather than 0 for records without parents (that's what NULL is there for, after all). Foreign keys can refer to the same table. You'll need to use InnoDB tables for the foreign key to apply.

CREATE TABLE `jlimited_test2`.`modules` (
   `module_id` int( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT
   , `module_sub_id` int( 11 ) UNSIGNED
   , `module_name` varchar( 100 ) CHARACTER SET utf8 NOT NULL
   , `module_active` int( 11 ) NOT NULL
   , PRIMARY KEY ( `module_id` )
   , FOREIGN KEY (`module_sub_id`) REFERENCES `modules` (`module_id`)
         ON DELETE SET NULL
         ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

Maintain column relationships in MySQL

1 Answers1