You can create the Foreign Key, but I don't think you will be able to cascade changes to Codigo / Loja to both locations in tblProjetos.An alternative would be to store tblClientes.ID in tblProjetos (i.e. store the ID instead of the Codigo+Loja) - so called "surrogate-key" - and then you can change Codigo+Loja in tblClientes as often as you like - anything that wants to display it will link from tblProjetos using the ID.You can create a VIEW to make it easier to get the related data from tblClientesSo instead of CodigoCliente + LojaCliente you have ClienteID, and instead of ClienteFinal + LojaClienteFinal you have ClienteFinalID.For example:CREATE VIEW V_ProjetosASSELECT ID AS V_ID, C1.Codigo AS CodigoCliente, C1.Loja AS LojaCliente, C2.Codigo AS ClienteFinal, C2.Loja AS LojaClienteFinalFROM tblProjetos AS P LEFT OUTER JOIN tblClientes AS C1 ON C1.ID = P.ClienteID LEFT OUTER JOIN tblClientes AS C2 ON C2.ID = P.ClienteFinalID
and then when you use tblProjetos you must JOIN to the View too - the column names will be the same as you already have (so no change to WHERE or SELECT)SELECT ID, Projecto, CodigoCliente, LojaCliente, CodigoClienteFinal, LojaClienteFinal, ...FROM tblProjetos AS P JOIN VProjetos AS V ON V.V_ID = P.ID
Double Foreign key to the same table
Posted - 2010-02-25 : 10:48:09
I need to create two links from tblProjetos to tblClientes, one for (CodigoCliente and LojaCliente) and another to (CodigoClienteFinal and LojaClienteFinal).Sorry because the tables is in Portuguese, but:Codigo means CodeLoja means StoreCodigoCliente means CustomerCodeLojaCliente means CustomerStoreCodigoClienteFinal means FinalCustomerCodeLojaClienteFinal means FinalCustomerStoreIf this is not possible, so i need to update tblProjetos every time my user changes Codigo or Loja from tblClientesThanks!