2

I have a MySQL database with two MySql tables. First is with The first table (table A) has a column with unique values(from values (from 1 to n). In the second table 2 (table B), I have two columns: in the first I have a name and in the second i have values from 1 to n(if i ad an value in the . The second it's updated column in table B is a reference to the first and vice versa. That's done)column in table A.

My Question: Can I limit the number of aparation of times the values from the second in table A appear in table B, specifically to a maximum of 10 times? An example is this: (with limitation for column val to 3) (the third table would send an error)

First table:      Second table:        Third table(imaginary)
+---+             +----+-----+          +----+-----+ 
|val|             |name| val |          |name| val |
+---+             +----+-----+          +----+-----+
| 1 |             |  a |  1  |          |  a |  1  |          
| 2 |             |  b |  2  |          |  b |  2  |
+---+             |  c |  1  |          |  c |  1  |
                  |  d |  1  |          |  d |  1  |
                  +----+-----+          |  e |  1  |
                                        +----+-----+

PS: Sorry for my english.

flix
  • 21
  • 3
  • Sorry, still not getting the question. Can you edit a little more? – MJB Apr 12 '11 at 16:59
  • please review my edit. I think we were editing at the same time and you saved after me, overwriting my changes. – AJ. Apr 12 '11 at 17:12

2 Answers2

1

You need to add a constraint to the table definition of the second table. This way, the database will check the value for you upon insert and update.

pjwilliams
  • 300
  • 2
  • 10
  • Vary good (and funny) response. I tried to create a variable that counts how many times the values are in the 2 table...but if i make an update in cascade...than my variables are chaotic – flix Apr 12 '11 at 17:30
0

I think you will need to use a trigger: http://forge.mysql.com/wiki/Triggers#Emulating_Check_Constraints

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • @flix are you saying YOU don't work with triggers or you've tried it with a trigger and you can't get it to work? – AJ. Apr 12 '11 at 18:02
  • is not really what i need....at that link, if I undestood correct, there you can test if the value is beetween 10 and 100...i nedd to count how many times the value appears and if it`s beetween 1 and the number i want(3 in the example) it` ok...else...error – flix Apr 12 '11 at 18:13
  • @flix Your BEFORE INSERT trigger will need to SELECT COUNT(*) from the table to determine if the limit was already reached. Aborting is really the tricky part: http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html#c8808 – Cade Roux Apr 12 '11 at 18:52