0

I have 3 tables : 

 ------------------------
|      Competitions      |
 ------------------------
| id (PK) | INT (auto++) |
| name    | VARCHAR(100) |
 ------------------------

 ------------------------------------
|              Matches               |
 ------------------------------------
| id (PK)  |     INT (auto++)        |
| blue1    | INT (FK -> teams.id) ** |
| blue2    | INT (FK -> teams.id) ** |
| blue3    | INT (FK -> teams.id) ** |
| red1     | INT (FK -> teams.id) ** |
| red2     | INT (FK -> teams.id) ** |
| red3     | INT (FK -> teams.id) ** |
| compID   | INT (FK -> comps.id)    |
 ------------------------------------

 ---------------------------------
|              Teams              |
 ---------------------------------
| id (PK)  | INT                  |
| name     | VARCHAR(100)         |
| compID   | INT (FK -> comps.id) |
 ---------------------------------

For the columns in Matches marked with a **, I want to foreign key to Teams.id, but I need to make sure that the compID in Matches and Teams are the same.

For example if Team 9987 is going to compID 1, it should not be red1,2,3 or blue1,2,3 in a match whose compID is 4.

How can I make this constraint?

Jay S.
  • 1,318
  • 11
  • 29
  • @Strawberry I know I need to normalize, but I'm not sure how to to it here. Could you show me? – Jay S. Jan 30 '16 at 00:59
  • MySQL doesn't support computed constraints like this. You can use triggers to check the values whenever a row is inserted or updated. – Barmar Jan 30 '16 at 01:21
  • If I knew what you were trying to do, perhaps. But right now, I've no idea. A column called colours might be one place to start. – Strawberry Jan 30 '16 at 01:26
  • I assume if your rep was one this would be half way toward closure as Unclear – Drew Jan 30 '16 at 01:43

0 Answers0