I have three tables that need to be linked like so:
Event(RiskID) -> Risk(RiskID)
Risk(ProjectID) -> Project(ProjectID)
Event(ProjectID) -> Project(ProjectID)
Using these tables:
Project Table
+------------+-----------+
| ID | ProjectID |
+------------+-----------+
And the bottom two tables that I am trying to link via a foreign key constraint
Risk
+------------+-----------+------------+
| ID | ProjectID | RiskID |
+------------+-----------+------------+
Event (aka, risk mitigation event, many events per one risk, and many risk per one project)
+------------+-----------+------------+-------+
| ID | ProjectID | EventID | RiskID|
+------------+-----------+------------+-------+
From my current understanding of Relational Databases like MySQL, to create a foreign key I should be using one Primary Key in the linked table to the table that needs to reference values, and for enforcing referential integrity. How do I accomplish this constraint if all three tables have duplicate values, namely for the attribute set {ProjectID,EventID} which are as such because all my tables have a history specific column information to track changes?
This sort of approach made me have some doubts about how to create the foreign key if necessary in this case. How do I use the ID inside my foreign key but still use the {ProjectID, EventID} {ProjectID, RiskID} together...etc...
If my terminology deviates from what is understood I can revise or clarify. Hope my question (on this topic of foreign keys) has a common solution?
I would add several bridge tables so that every projet kann have many risks and multipole event
I am not ver clear why you have another primary unique key, but ok evrybiody can do what they like
Every column should be only in one
project
Proj_IG(PK) | Project_ID(KEY)
event_project
Project_ID(FK) | Envent_ID(FK
event
Evnt_ID | Envent_ID....._
If the event project and risks are interliked, you could make a bridge table with three colums, so could a project could ave also mutile event and risks but tas tehy are all conected the bridge table yan represent that
risk_project
RIsk_ID Projekt_ID
risk
R_ID | Risk_ID
project
Proj_IG(PK) | Project_ID(KEY)
event_risk project
Project_ID(FK) | Envent_ID(FK) | Risk_ID(FK)
event
Evnt_ID | Envent_ID....._
risk
R_ID | Risk_ID