-2

Question 1:

There are 3 Tables Direct Customers, Agent Customers, Sales

Primary keys are marked in red underline:

enter image description here

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).

Question 2:

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?

3

You'll need to add columns to your Sales table or create joining tables between Direct_Customer and Agent_Customer to Sales as you cannot place multiple Foreign Keys on the same column. Joining tables are the preferred method to retain a traditional relational model:

CREATE TABLE Direct_Customer
(    
    DC_ID INT IDENTITY(1,1)
    ,DC_Name NVARCHAR(100)
    ,CONSTRAINT [PK_DirectCustomer] PRIMARY KEY (DC_ID)
)
GO
CREATE TABLE Agent_Customer
(    
    AC_ID INT IDENTITY(1,1)
    ,AC_Name NVARCHAR(100)
    ,CONSTRAINT [PK_AgentCustomer] PRIMARY KEY (AC_ID)    
)
GO
CREATE TABLE Sales
(    
    S_ID INT IDENTITY(1,1)
    ,date DATETIME
    ,Sales_Type INT
    ,CONSTRAINT [PK_Sales] PRIMARY KEY (S_ID)    
)
GO
CREATE TABLE Sales_DirectCustomer
(
    SDC_ID INT IDENTITY(1,1)
    ,DC_ID INT
    ,S_ID INT
    ,CONSTRAINT [PK_Sales_DirectCustomer] PRIMARY KEY (SDC_ID)
    ,CONSTRAINT [FK_Sales_DirectCustomer_DirectCustomer] FOREIGN KEY (DC_ID) REFERENCES [dbo].[Direct_Customer](DC_ID)
    ,CONSTRAINT [FK_Sales_DirectCustomer_Sales] FOREIGN KEY (S_ID) REFERENCES [dbo].[Sales](S_ID)
)
GO
CREATE TABLE Sales_Agent_Customer
(
    SAC_ID INT IDENTITY(1,1)
    ,AC_ID INT
    ,S_ID INT
    ,CONSTRAINT [PK_Sales_AgentCustomer] PRIMARY KEY (SAC_ID)
    ,CONSTRAINT [FK_Sales_AgentCustomer_AgentCustomer] FOREIGN KEY (AC_ID) REFERENCES [dbo].[Agent_Customer](AC_ID) 
    ,CONSTRAINT [FK_Sales_AgentCustomer_Sales] FOREIGN KEY (S_ID) REFERENCES [dbo].[Sales](S_ID)
)

If you can't create new tables you can instead do the following:

ALTER TABLE Sales ADD AC_ID INT NULL
GO
ALTER TABLE Sales
ADD CONSTRAINT [FK_Sales_AgentCustomer] FOREIGN KEY (AC_ID) REFERENCES [dbo].[Agent_Customer](AC_ID); 
GO
ALTER TABLE Sales ADD DC_ID INT NULL
GO
ALTER TABLE Sales
ADD CONSTRAINT [FK_Sales_DirectCustomer] FOREIGN KEY (DC_ID) REFERENCES [dbo].[Direct_Customer](DC_ID); 
GO

However, this will result in possible NULL values if only one of the two tables is involved in a particular Sale row and can make querying more difficult down the line. I would not recommend this approach if you can avoid it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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