MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time

Question

I want to create a pair of table with a structure along the lines of:

CREATE TABLE tableA (
    ext_id bigint NOT NULL,
    local_id int AUTO_INCREMENT NOT NULL, /* <-- Requires MyISAM (InnoDB doesn't support this) */
    PRIMARY KEY (ext_id, local_id),
    );

CREATE TABLE tableB (
    ext_id bigint NOT NULL,
    local_id int NOT NULL,
    CONSTRAINT FOREIGN KEY(ext_id, local_id) REFERENCES tableA(ext_id, local_id) ON DELETE CASCADE ON UPDATE CASCADE /* <-- Requires InnoDB (MyISAM doesn't support this) */
    );

However as stated in the comments these features seam to be mutually exclusive based on the database engines that support them i.e. InnoDB doesn't support auto_increment on local_id on tableA, and MyISAM doesn't support tyhe FOREIGN KEY in tableB referencing tableA.

I'm looking for a way to simulate the missing feature on one of the database engines.

I could use a trigger to simulate the ON DELETE/UPDATE CASCADE part of the foreign key, but I don't believe this will work for enforcing the existence of (ext_id, local_id) in tableA when inserted into tableB. Beyond this I have no ideas on how I can solve this problem.

Answer 1

I discuss the problem here, including a couple of workarounds.

The simple workaround is to have local_id be AUTO_INCREMENT and abandon the requirement for the values starting over at 1.

Keep in mind that ROLLBACK may cause you to lose ids.

Answer 2

After further research and some experimenting I found a way to simulate foreign keys using triggers in MyISAM

CREATE TRIGGER tableB_before_insert BEFORE INSERT ON tableB FOR EACH ROW BEGIN
    IF NOT EXISTS(SELECT * FROM tableA WHERE ext_id = NEW.ext_id AND local_id = NEW.local_id) THEN
        SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = "Entry dosen't exist in tableA";
    END IF;
END

CREATE TRIGGER tableB_before_update BEFORE UPDATE ON tableB FOR EACH ROW BEGIN
    IF NOT EXISTS(SELECT * FROM tableA WHERE ext_id = NEW.ext_id AND local_id = NEW.local_id) THEN
        SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = "Entry dosen't exist in tableA";
    END IF;
END

CREATE TRIGGER tableA_after_delete AFTER DELETE ON tableA FOR EACH ROW BEGIN
    DELETE FROM tableB WHERE ext_id = OLD.ext_id AND local_id = OLD.local_id;
END

CREATE TRIGGER tableA_after_update AFTER UPDATE ON tableA FOR EACH ROW BEGIN
    UPDATE tableB SET ext_id = NEW.ext_id, local_id = NEW.local_id WHERE ext_id = OLD.ext_id AND local_id = OLD.local_id;
END

Notes: Triggers need to be BEFORE triggers on tableB so that the signal which should be 40xxx will abort the operation, MESSAGE_TEXT can be what ever this is the text displayed in the error message. While the trigger need to be AFTER triggers on tableA this way if the operation fails the trigger doesn't get called, correct syntax is 'tableB field name' = OLD.'tableA field name' in the where clause, and the but NEW in the set clause this searches on the original values of the row in tableA and either deletes or updates to the new values.