Mysql constraint problem

I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this:

+------------+   +------------+    +------------+
| t1         |   | t2         |    | t3         |
+------------+   +------------+    +------------+
| t1_id (PK) |   | t2_id (PK) |    | t3_id (PK) |
+------------+   | some_value |    | t1_id (FK) |
                 +------------+    | t2_id (FK) |
                                   +------------+

PK = primary key, FK = foreign key, and all fields are of type INT

The problem I have is that I want to add a constraint to table t3 that involves the field t2.some_value. Basically I want the combination of t1.t1_id and t2.some_value to be unique within table t3. For example here is a situation I want to avoid:

t1           t2                        t3
+-------+    +-------+------------+    +-------+-------+-------+
| t1_id |    | t2_id | some_value |    | t3_id | t1_id | t2_id |
+-------+    +-------+------------+    +-------+-------+-------+
| 10    |    | 20    | 1          |    | 30    | 10    | 20    | <-- t2.some_value = 1
| 11    |    | 21    | 2          |    | 31    | 10    | 21    |
+-------+    | 22    | 3          |    | 32    | 10    | 22    |
             | 23    | 1          |    | 33    | 10    | 23    | <-- t2.some_value = 1
             +-------+------------+    | 34    | 11    | 23    |
                                       +-------+-------+-------+

The problem here is that two records in t3 link the same t1 record (t1_id=10) to two different t2 records that have the same some_value. Is there any way to prevent this from happening? To my knowledge MySql does not allow constraints to be defined that span multiple tables.

I can think of one solution, but that involves duplicating the some_value field in table t3 so I can enforce a unique key directly on it. This obviously breaks the normalisation design rule, but is there any other way?

From what I can tell, you'll have to create the 'some_value' in table3. Here's a link to the manual page on foreign key constraints

There is an example with a 'complex' foreign key constraint that is similar to your setup, which duplicates the content in the third table. There is (minimal) comfort in knowing that hey, at least it's handled by a Foreign Key.

StackExchange Database Administrators Q#2802, answer score: 2