MySQL conditional referencing of foreign key

Question

I'm currently working on a schema with nullable foreign key. Basically, this is what I want to achieve:

Given:

table 1:

game:
  id
  observer_id
  starts_on
  ends_on
  type

table 2:

observer:
  id
  game_id DEFAULT NULL
  starts_on
  ends_on
  type
  FOREIGN KEY (`game_id`) REFERENCES `game`(`id`) ON DELETE SET NULL

Now, what I want to do - I want to duplicate as the initial values and update on cascade starts_on, ends_on and type fields in observer table if I have a reference to a game, however if game_id is null I want to have an independent values for above mentioned fields. Is something like this possible with IF in mysql or should I implement the logic in my model files?

Answer 1

Consider to solve your issue while data retrieving (SELECT) instead of data manipulation (INSERT/UPDATE/DELETE).

select
    o.id,
    o.game_id,
    coalesce(g.starts_on, o.starts_on) as starts_on,
    coalesce(g.ends_on,   o.ends_on)   as ends_on,
    coalesce(g.type,      o.type)      as type
from observer o
left join game g on g.id = o.game_id

In this query if o.game_id is NULL the LEFT JOIN will find no matches in the game table and all its columns will also be NULL. Then COALESCE() will take care of choosing the value from the game table if present or from observer otherwise.

You can use that query in a VIEW (if you like), but i can't tell if it will perform well in any use case.

This way you can store your "independent" values without worrying if the game id is set or not, since they will only be used, if the game_id is not NULL.

Note that your problem might be solved in a more "clean" way changing the schema. With the columns game.observer_id and observer.game_id it looks like a cyclic relation, which can be considered as "design smell". But without knowledge of your data logic I can't suggest a better way.