0

With the UNIQUE constraint, I can make sure that no duplicate values are entered in a particular field on a table. Is there a method for allowing only certain number of duplicates? For example, if it were possible to limit the max duplicate values to 2, for the field animal, I could have 2 rows with the animal field having a value of frog, but any further attempts to insert a row with the animal field set to frog would fail.

I understand that I might be showing an embarrassing lack of data normalization skill here. I would appreciate any suggestions on workarounds as well.

Thanks!

AnalogWeapon
  • 550
  • 1
  • 4
  • 16
  • https://stackoverflow.com/help/how-to-ask – coder001 Feb 27 '18 at 04:09
  • You can use triggers, or you can embed the logic into the query itself – Strawberry Feb 27 '18 at 08:28
  • @coder001 Maybe someone should make a document for "How to Provide a Useful Answer". I searched, wrote a title that summarizes my problem, I introduced the problem (For which there is no code that I'm aware of because I haven't found any method for doing this particular constraint), I proofread, and now I'm responding to (non-constructive) feedback... – AnalogWeapon Feb 27 '18 at 13:29

2 Answers2

1

You can achieve this using triggers.

https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

MySQL Trigger to prevent INSERT under certain conditions

Iynga Iyngaran Iyathurai
  • 796
  • 1
  • 9
  • 14
  • Aha. I think this is what I'm looking for. Particularly, I should be able to use `SIGNAL` to find a way to stop the transaction as described [here](https://stackoverflow.com/a/8559284/1530115). My problem was that I wasn't aware of what triggers were for, so they weren't part of my searching. Thanks! – AnalogWeapon Feb 27 '18 at 13:39
0

Try this:

INSERT INTO `table` (`animal`) SELECT 'frog' FROM 
`table` WHERE `animal` = 'frog' HAVING 
COUNT(`animal`) < 4
Kannan K
  • 4,411
  • 1
  • 11
  • 25