CODEWITHSUNDEEP
mysqlsqlphpmyadmin
flix
flix

Reputation: 21

How to limit number of the many in a one-to-many relationships between two tables?

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.

Upvotes: 2

Views: 2496

Answers (2)

Cade Roux
Cade Roux

Reputation: 89661

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

Upvotes: 0

pjwilliams
pjwilliams

Reputation: 288

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.

Upvotes: 1

Related Questions