Need help with mysql schema design - current schema requires dynamic sql within a trigger

Question

I imagine that I have designed my database badly, but I'm currently stumped by the fact that I need to use dynamic sql in a trigger and that's making mysql unhappy.

The context is that I have created a membership database with several dozen tables, the main one of which is the 'member' table with a unique primary key 'id'. There are a number of other tables which have foreign keys referring to the member.id field.

Because the data has been gathered over many years and with little dupe-control, there is another field in the 'member' table called 'superseded_by', which contains the id of the member who supersedes this one. By default, superseded_by is set to be the member_id. Any one whose superseded_by <> id is deemed to be a dupe.

Now the tricky part... when we identify a dupe, we want to set the superseded_by field to point to the new primary member and update all the tables with foreign keys pointing to the now redundant member id. I have tried to do this using an after update trigger... and then I've tried to be clever by querying the foreign keys from the information_schema and using dynamic sql to update them.

This clearly doesn't work (Error Code: 1336 Dynamic SQL is not allowed in stored function or trigger).

I'm assuming there is a better way to design the schema / handle dupes which I haven't thought of.

Help please...

CODE SNIPPET:

-- ---
-- Table 'member'
-- ---
DROP TABLE IF EXISTS member;
CREATE TABLE member (
    id INTEGER AUTO_INCREMENT,
    superseded_by INTEGER DEFAULT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('M', 'F') DEFAULT NULL,
    mailing_address_id INTEGER DEFAULT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (mailing_address_id) REFERENCES mailing_address (id),
    FOREIGN KEY (superseded_by) REFERENCES member (id)
);
DELIMITER $$
    CREATE TRIGGER set_superseded_by_on_insert BEFORE INSERT ON member FOR EACH ROW
    BEGIN
        SET NEW.superseded_by = NEW.id;
    END$$

    -- Trigger to update other tables (volunteers, donations, presenters, etc.) when member's superseded_by record is updated
    -- Assumes the new superseding person exists (they should also not be superseded by anyone themselves)
    CREATE TRIGGER adjust_foreign_member_keys_on_superseded_by_update AFTER UPDATE ON member FOR EACH ROW
    BEGIN
        DECLARE db, tbl, col VARCHAR(64);
        DECLARE fk_update_statement VARCHAR(200);
        DECLARE no_more_rows BOOLEAN;
        DECLARE fks CURSOR FOR  SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME
                                FROM information_schema.TABLE_CONSTRAINTS tc
                                JOIN information_schema.KEY_COLUMN_USAGE kcu ON
                                        tc.table_schema = kcu.table_schema AND tc.constraint_name = kcu.constraint_name
                                WHERE   tc.constraint_type='FOREIGN KEY' AND
                                        kcu.REFERENCED_TABLE_NAME = 'member' AND
                                        kcu.REFERENCED_COLUMN_NAME = 'id';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

        IF NEW.superseded_by <> OLD.superseded_by THEN

            OPEN fks;
            SET no_more_rows = FALSE;
            update_loop: LOOP
                FETCH fks INTO db, tbl, col;
                IF no_more_rows THEN
                    LEAVE update_loop;
                END IF;
                SET @fk_update_statement = CONCAT("UPDATE ", db, ".", tbl, " SET ", col, " = NEW.superseded_by WHERE ", col, " = NEW.id;");
                PREPARE stmt FROM @fk_update_statement;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
            END LOOP;
            CLOSE fks;

        END IF;
    END$$
DELIMITER ;

Answer 1

Why are you trying to maintain duplicates in your main table? Seems like you'd be better off with a member table and a member_history table to track previous changes. You could do it by having a table that stored the field changed, date changed and the old and new values. Or you could just store the previous snapshot of the member table before updating it. For instance:

INSERT INTO member_history SELECT NULL, * FROM member WHERE id = ?
UPDATE member SET [...] WHERE id = ?

The schema for member_history would be nearly identical except that you would store member.id as member_id and have a separate primary key for each history entry. (Note: I'm glossing over the syntax a little, the NULL, * part might not work in which case you may need to explicitly name all the fields. Haven't taken the time to check it).

CREATE TABLE member (
    id INTEGER AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('M', 'F') DEFAULT NULL,
    mailing_address_id INTEGER DEFAULT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (mailing_address_id) REFERENCES mailing_address (id),
);

CREATE TABLE member_history (
    id INTEGER AUTO_INCREMENT,
    member_id INTEGER NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    gender ENUM('M', 'F') DEFAULT NULL,
    mailing_address_id INTEGER DEFAULT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (member_id) REFERENCES member (id),
);

Notice that I removed the superseded_by field in the member table and the foreign key to mailing_address in the member_history table. You shouldn't need the superseded_by any more and keeping the foreign key in the member_history table isn't really necessary unless you're worried about dangling references in your history.

Answer 2

Trigger and stored function in mysql have limitations that we can not use dynamic sql in both of these. I hope this helps.

Answer 3

Ok, just a couple of thoughts on this:

superseded_by is referencing id on the same table and is in general equal to the latter - not in those cases where you were able to identify a dupe, though, in which case it would point to another already existing member's id.

Given that we can safely assume that no superseded_by field will ever hurt the foreign key constraint.

I further assume that id and superseded_by fields of dupes that have not been identified yet are equal.

So, if all of the above is true, you may bend the foreign key of the other related tables to reference superseded_by instead of id. This way you could cascade the changes made to the dupe down to the other tables and still have the exact same constraint as before.

What you think? Am I missing something?

Please note that this is an option only if you are using InnoDB rather than MyISAM.

Regards, aefxx