1

My table has the following table:

  1. teams
  2. leagues
  3. players
  4. coaches
  5. keywords

Now, in the keywords table, there are few columns: id, topic_id, topic_type.

For teams, leagues, players and coaches they have multiple keywords(one-to-many relationship with keywords). For instance: if there is team ABC with id 4 in team table; in keywords table, there will be id, 4, team. If there is player CDE with id 3 in the player table; in keywords table, there will be id,3, player and so on.

So, here what I'm trying to do is adding a constraint in keywords that will allow me to delete the keyword when the topic is deleted from any topic_type. How do I do it? We can easily do it via Schema ondelete Cascade from reading some of the answers, but in my case, there should be a constraint on 1 item but from multiple sources.

How can I set a constraint on keywords table from all teams, leagues, players and coaches?

Thank you

GMB
  • 216,147
  • 25
  • 84
  • 135
Nish Dekardo
  • 329
  • 2
  • 11
  • Databases have tables. Tables have columns. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Mar 04 '20 at 23:32
  • Having one field possibly reference multiple tables is a bad design. Your best best is simply a separate "child" table for keywords for each "parent" table. – Uueerdo Mar 05 '20 at 00:51
  • Yes I am agreed with @Uueerdo You have to normalize your DB first. Using a bad design leads to many consequences. – Ashok Dhaduk Mar 06 '20 at 02:04

1 Answers1

0

I think that what you cask for cannot do be done with constraint in MySQL.

An alternative option would be to modify your database structure. You could have separated tables to store the relations between each entity (teams, league, players, coaches) and the keywords.

So you would create, four, additonal, bridge tables:

teams_keywords  (team_id,    keyword_id)
league_keywords (league_id,  keyword_id)
players_keywords(player_id,  keyword_id)
coaches_keywords(coaches_id, keyword_id)

Each table a has foreign key that relates to an entity table, and another that links the keyword table. You can use option on delete cascade on these foreign keys to enfore the logic that you want.

Note that such structure might also simplify some of your queries. Say you want to get all teams that match a given keyword, then:

select t.*
from teams t
inner join team_keywords tk on tk.team_id = t.team_id
inner join keywords k on k.keyword_id = tk.keyword_id
where k.name = 'foo'
GMB
  • 216,147
  • 25
  • 84
  • 135
  • OP isn't planning on sharing keywords so this design would actually prevent the deletion of a team from cascading to the keywords for that team. – Uueerdo Mar 05 '20 at 00:55
  • @Uueerdo: could be, yes, that OP doesnt want to share (although I would expect something called *keywords* to be meant for that). In that event, we don't need the `keywords` table, just a separate child table for each entity. – GMB Mar 05 '20 at 00:59