0

I have a situation where i have a chatbot which can support multiple languages . To store this relationship in sql I have created Bot and language mapping table which if bot have 3 languages will create 3 rows in mapping table in mysql. I also have another column called is_primary in this mapping table which tells whether this is the bot's primary language. Here i want to ensure that the bot always have atleast and atmost one primary langauge. Is there a way to put constraint in combination of rows to ensure this.

Thanks

dilkash
  • 562
  • 3
  • 15
  • To ensure this condition on database level in MySQL you'll need to create three triggers. Unique index wont help much. – olegsv Nov 22 '19 at 08:08
  • @olegsv yeah can do that but first will try to find simple solution. Thanks – dilkash Nov 22 '19 at 08:31

1 Answers1

0

AFAIK, we cannot have such constraint, and this has to be managed in code

However, instead of using is_primary field, if you normalize the structure and have a separate table for bot and language mapping then you can use composite primary key to ensure each both has exactly one primary language.

Hemang
  • 1,351
  • 1
  • 10
  • 20