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.

• ypercubeᵀᴹ: So you want tableA.local_id values to be 1,2,3... for each ext_id, correct?
• Glen Fletcher: Yes, this is correct.

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.

• Glen Fletcher: 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.
• Rick James: @glenflet -- "requirement" - referring to starting at 1 for each? WP needs some serious DBA help; it has multiple scaling problems.

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.