[thelist] yet another database/table relationship design question

[Paul Cowan]

Chris Blessing wrote:
> However I'm running into an issue when trying to insert data into the
> Products table.  If I try to insert a Product_Code into Products, I get a
> foreign key constraint conflict in the Ancillaries table.  I assume SQL
> Server would throw the same error on the Journals table if it was the only
> child table of Products.  Should I be getting this error?

Ummm.... no. If, in the quoted database design, you're doing something like
    INSERT INTO Products(Product_Code) VALUES ('PRODUCT0001')
then that should work.

It sounds like (it would be helpful to see the exact error message) that
you've got your foreign key the wrong way around. Product is the primary
key table; Ancillaries should be the foreign key table. I suspect that you
have this the other way around, or that you have created the FK between
Product.Product_ID and Ancillaries.Ancillary_ID by mistake.


> So to ask my real question, how can I create such a mutually exclusive
> relationship?  Or do I need to do something completely different?  Any
> insights are greatfully welcomed!

Short answer? No. Not with foreign keys anyway. You need an FK from
Product.Product_ID (primary) to Journals.Product_ID (foreign), and one
from Product.Product_ID (primary) to Ancillaries.Product_ID (foreign),
but you know this.

Ideally you would then do an "reverse foreign key" between
Journals.Product_ID and Ancillaries.Product_ID, saying "allow no
duplicate values", but since I just made up "reverse foreign keys",
and they do not in fact exist in SQL Server, you might have a bit of a
problem there.

If you need to enforce, DB-side, that no duplicate product_IDs exist
in the Journals and Ancillaries table, then your best bet would be
to create a trigger on each table, which checks the values, like so
(my trigger syntax is a bit dodgy, check the manuals; I haven't done
a trigger in a few months)


CREATE TRIGGER TheMagicFarawayTrigger ON Journals FOR UPDATE, INSERT AS
    IF EXISTS(
        SELECT *
        FROM Ancillaries
        WHERE Ancillaries.Product_ID IN (SELECT Product_ID FROM Inserted)
    )
    BEGIN
        -- couple of things you could do here.
        -- rollback, maybe?
        -- or RAISERROR?
        -- or something else, depending on what you want to do.
    END


and, correspondingly, the opposite trigger on the Ancillaries table.

Of course, there is an even simpler solution, but I assume you've
discounted it already. If, say, the Ancillaries and Journals tables held
fundamentally the same data, and the difference was purely conceptual, then
you could merge them into one table, the AncillariesAndJournals table,
which has either a "TypeID" or a bit flag or something which indicates
if it's an ancillary or a journal (though if you actually CALLED
the table "AncillariesAndJournals", and I ever had to work on that table
after you, I would hunt you down like a dog because that's an AWFUL name,
but you get the idea...).

I'm guessing, though, that if you've made each one its own table, there's
a good reason for that. It's probably an awful idea, I just thought I'd
suggest it anyway!

Hope this helps,

Paul