0

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.

  • So you want tableA.local_id values to be 1,2,3... for each ext_id, correct? – ypercubeᵀᴹ Jun 6 '17 at 11:11
  • Yes, this is correct. – Glen Fletcher Jun 8 '17 at 9:02
0

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.

  • I had considered that possibility, however I would like a solution that doesn't abandon this requirement. This is aimed at a multisite system using a common software (wordpress), ext_id is actually a site id this is to avoid possibly creating several thousand tables (i.e. two for each site), hence all table still need the ext_id, and the local_id would need to become bigint to do this (to support max number of entries today). Since this is a plugin I intend to public release, I need to account for large numbers of entries then I myself would need. – Glen Fletcher Jun 8 '17 at 9:28
  • @glenflet -- "requirement" - referring to starting at 1 for each? WP needs some serious DBA help; it has multiple scaling problems. – Rick James Jun 8 '17 at 19:03
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.