Foreign-Key constraint depending on category

Question

I have a m:n connection between the table "Bill" and the tables "GeneralArticles", "CalculatedArticles" and "AdditionalRecords".

These tables are straight-forward. Each has its own ID (Let's call them B_ID and for the other ones GA_ID, CA_ID and AR_ID) and some additional fields. The question is how to set up one m:n-connection-table between Bill and these three tables guaranteeing referential integrity?

My first idea was to simply create:

CREATE TABLE Bill_Articles(
[B_ID] [int] NOT NULL FOREIGN KEY REFERENCES Bill (B_ID) ,
[A_ID] [int] NOT NULL ,
Category [int] NOT NULL
CONSTRAINT [PK_Bill_Articles] PRIMARY KEY CLUSTERED 
            (
            [B_ID] ASC,
            [A_ID] ASC,
            [Category] ASC
            ))

I've set up a table for the Categories to, so theoretically I could use 1 for General Articles, and so on, you get the idea. Yet the problem is how is it possible to refer to another table depending on the Category?

Another solution would be to create a View which contains this Category-ID and the ID of the Record:

Create View Articles
AS
Select 1 Category,
        GA_ID A_ID FROM GeneralArticles
UNION 
SELECT 2 Category,
       CA_ID A_ID FROM CalculatedArticles
UNION 
SELECT 3 Category,
        AR_ID  A_ID  
        FROM AdditionalRecords

Unfortunately when setting up the Bill_Articles table I get the error:

Foreign key 'FK__Bill_Ar_A_ID__7526B52E' references object 'Articles' which is not a user table.

Somehow I can't use this kind of View for providing unique keys.

Does anyone know a proper solution for this kind of problem? Thank you

What is the difference between the article tables, can you show your schema? — Steve Ford, Jan 08 '15 at 11:12
We're dealing with a grown system unfortunately which generates huge tables in German... But in a nutshell I try to explain: _GeneralArticles_ are just selected Articles from the Article-Database with selected prices and a date. _CalculatedArticles_ is practically the same but refers to another tables too - which contain specific quantities needed for the usage of these articles, also it gives information about article-groups. _AdditionalRecords_ are Articles which don't exist in the system and can be added by the administrator. (Some articles are ordered only once or twice per year). — Qohelet, Jan 08 '15 at 11:30

score 1 · Accepted Answer · answered Jan 08 '15 at 11:18

Usually you don't create one table with a column for category for these very reasons, that you can't have proper foreign keys.

You should create several tables.

One table to link Bills and GeneralArticles. Second table to link Bills and CalculatedArticles. Third table to link Bills and AdditionalRecords.

For the first link it may look like this (I didn't check the syntax):

CREATE TABLE BillsGeneralArticlesAssosiations(
[B_ID] [int] NOT NULL FOREIGN KEY REFERENCES Bill (B_ID) ,
[GA_ID] [int] NOT NULL FOREIGN KEY REFERENCES GeneralArticles (GA_ID)
CONSTRAINT [PK_BillsGeneralArticlesAssosiations] PRIMARY KEY CLUSTERED 
(
    [B_ID] ASC,
    [GA_ID] ASC
))

Sometimes I add an IDENTITY column ID to this table as a primary key. Especially if there are other fields in this linking table.

I get the idea but haven't thought of that yet. I like the idea with the `IDENTITY`-column but in this case I don't think this will be necessary. I'll probably add a `ON DELETE CASCADE` — Qohelet, Jan 08 '15 at 11:35
I would call this design of linking tables a "proper solution". Also, I personally use `ON DELETE CASCADE` very rarely. It is set on only 16 tables out of 343 in our system. I prefer to delete related data explicitly if needed. I don't like the risk of wiping out records in dozens related tables when trying to delete an incorrect row by mistake. — Vladimir Baranov, Jan 08 '15 at 11:46

Foreign-Key constraint depending on category

1 Answers1