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?
2 Answers
Answers 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.
Answers 2
FOREIGN KEYs do a limited number of things. Once you go beyond them, you should build your own transaction in your app code (or Stored Procedure or ...) to do the more complex things.
I feel that this is the safer route, since you know exactly what you need, and won't have to try to shoehorn FKs into doing it.
0 comments:
Post a Comment