Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
I really like the idea of using t2_id,some_value together as a foreign key. This ensures that the duplication of 'some_value' in the 3rd table can never be in conflict with the 2nd table. Also, as 'some_value' is now acting as a key it feels like data normalisation hasn't been broken! –  David May 19 '11 at 12:39
    
i still get a bad taste in my mouth at data duplication, but what can you do? :D –  Derek Downey May 19 '11 at 13:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.