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.