There are 3 Tables Direct Customers, Agent Customers, Sales
Primary keys are marked in red underline:
How to set foreign key for sales table using other two tables. I want foreign key enabled for:
Sales Table - Sales Type (it should be Foreign Key for either "Direct Customer" or "Agent Customer") in Sales Table.
Sale can be made to either direct customer (dc_id) or through agent customer (ac_ID). For example, only agent through sales went I can assign Agent ID as a foreign key in Sales table so in sales table no need to define Sales_Type. The thing is not only one table interconnect to sales table. Can I use Direct customer ID (DC_ID) one foreign key and Agent Customer (AC_ID) another customer in sales table? There is a problem in the select when sales table (inner join).
Note: If I select Direct Customer/Agent Customer in Sales Type in Sales Table. I don't know how to assign foreign key in Sales Table (Sales Type).
After assigning foreign key in sales table how to select sales table (Direct customer wise, Agent wise, and all together)? Can you please show an example in SQLFiddle?