MySQL create both entities with cyclic foreign key

Question

I want to have an "Entity" and many versions of it, where one of those versions is the only one which is active/used. It is also possible that the Entity is entirely deactive. So I thought of using two tables with cyclic foreign keys like this:

CREATE TABLE entity (
  id int NOT NULL AUTO_INCREMENT,
  -- some extra irrelevant data commented out
  active_version_id int DEFAULT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE entityversion (
  id int NOT NULL AUTO_INCREMENT,
  -- some extra irrelevant data commented out
  entity_id int NOT NULL,
  PRIMARY KEY (id)
);
ALTER TABLE entity ADD FOREIGN KEY (active_version_id) REFERENCES entityversion(id) ON DELETE SET NULL;
ALTER TABLE entityversion ADD FOREIGN KEY (entity_id) REFERENCES entity(id) ON DELETE CASCADE;

I would like to, when creating a new active Entity, to create at the same time its first EntityVersion which will be its active_version. The problem is we don't have their ids yet. Currently, we're creating the Entity with "returning id" and using that to create the EntityVersion, also with "returning id", and then updating the active_version_id of that same Entity, so 3 separate commands like this for example:

INSERT INTO entity DEFAULT VALUES RETURNING id;
-- get the ID back and use it as a parameter to the next command
INSERT INTO entityversion (entity_id) VALUES (%s) RETURNING id;
-- again the same thing
UPDATE entity SET active_version_id = %s WHERE id = %s;

I would like to know if there is a shorter way to do this. I also accept as answer a different approach to the table schemas, if it happens to be the better choice. Thanks for the help!

Answer 1

Create both your rows in a stored procedure, or use a before insert trigger if there is no data that only goes in the entityversion version table. To deal with your cyclical id problem, in mariadb use a sequence instead of auto_increment. In mysql, emulate a sequence with an entity_sequence table that only contains the auto_increment id. In your stored procedure/trigger, get the sequence value (with insert..returning id if emulating a sequence), store entityversion using that value, then set the entityversion id to store in your entity row.

Answer 2

You are implying that the entities are 1:1, in which case they may as well be in the same table. (Make one of the NULLable if it is not to inserted until later.)

If it is 1:many (a 'latest' and many 'older' versions), then the FK only goes one way.

In either case, your "circular" FKs go away.

But to answer your question:

1. Turn off FK checks
2. CREATE both tables
3. Populate both tables
4. ALTER to add both FKs
5. Turn on FK checks.

More

Well, it seems that you have many:1, not 1:1. The "History" has a column that is the "id" into the "Current" ('active') table. No circular FKs. Index that column so you can go the other way efficiently. ON DELETE CASCADE is not practical in either direction.

The FK should go one direction, not both.