Ensure foreign key of a foreign key matches a base foreign key

Question

Basically let's say I have a "Business" that owns postal codes that it services. Let's also suppose I have another relational table that sets up fees.

CREATE TABLE [dbo].[BusinessPostalCodes] 
(
    [BusinessPostalCodeId]         INT           IDENTITY (1, 1) NOT NULL,
    [BusinessId]                   INT           NOT NULL,
    [PostalCode]                    VARCHAR (10)  NOT NULL
)

CREATE TABLE [dbo].[BusinessPostalCodeFees]
(
    [BusinessId] INT NOT NULL, 
    [BusinessProfileFeeTypeId] INT NOT NULL,
    [BusinessPostalCodeId] INT NOT NULL, 
    [Fee] SMALLMONEY NULL
)

I want to know if it's possible to set up a foreign key (or something) on BusinessPostalCodeFees that ensures that the related BusinessId of BusinessPostalCodes is the same as the BusinessId of BusinessPostalCodeFees.

I realize that I can remove BusinessId entirely, but I would much rather keep this column and have a way of guaranteeing they will be the same. Is there anything I can do?

write your logic in trigger and rollback the transaction that violates your rule — techspider, Oct 03 '16 at 19:56
@techspider Thanks for the hint, but I think a checked constraint worked really well here. — Zachary Dow, Oct 19 '16 at 16:21

score 1 · Answer 1 · edited May 23 '17 at 12:01

1

It sounds like (and correct me if I'm wrong) that you're trying to make sure that any entry into BusinessPostalCodeFees' BusinessId and BusinessPostalCodeId columns match an entry in the BusinessPostalCodes table. If that's the case, then yes, you can definitely have a foreign key that references a compound primary key.

However, if you need to keep the BusinessId, I'd recommend normalizing your tables a step further than you have. You'll end up with duplicate data as-is.

On a side note, I would recommend you don't use the money data types in SQL: See here.

edited May 23 '17 at 12:01

Community

1
1

answered Oct 03 '16 at 20:08

Jeffrey Van Laethem

2,601
1
20
30

I totally didn't think about going at it in that way, but it makes a lot of sense. I think you're right though in that I ultimately should normalize it out as I look at it a little harder. This is great info, Thank you! – Zachary Dow Oct 03 '16 at 20:13
One follow up if you don't mind. The PostalCodes and FeeTypes both have a BusinessId. Is there any way in the relational table to ensure those match? – Zachary Dow Oct 03 '16 at 20:21
Nevermind, keeping the column and doing the double composite key for both columns works the way I want it. Thanks again! – Zachary Dow Oct 03 '16 at 20:31

Zachary Dow · Accepted Answer · 2016-10-19T00:26:10.977

In the end, Jeffrey's solution didn't quite work for my particular situation. Both columns in the relation have to be unique (like a composite key). Turns out the answer here (for me) is a Checked Constraint.

Create a function that you want to have the constraint pass or fail:

CREATE FUNCTION [dbo].[MatchingBusinessIdPostalCodeAndProfileFeeType]
(
    @BusinessId int,
    @BusinessPostalCodeId int,
    @BusinessProfileFeeTypeId int
)
RETURNS BIT
AS
BEGIN

    -- This works because BusinessPostalCodeId is a unique Id.
    -- If businessId doesn't match, its filtered out.   
    DECLARE @pcCount AS INT 
    SET @pcCount = (SELECT COUNT(*) 
        FROM BusinessPostalCodes 
        WHERE BusinessPostalCodeId = @BusinessPostalCodeId AND 
            BusinessId = @BusinessId)


    -- This works because BusinessProfileFeeTypeId is a unique Id.
    -- If businessId doesn't match, its filtered out.   
    DECLARE @ftCount AS INT 
    SET @ftCount = (SELECT COUNT(*) 
        FROM BusinessProfileFeeTypes
        WHERE BusinessProfileFeeTypeId = @BusinessProfileFeeTypeId AND 
            BusinessId = @BusinessId)

    -- Both should have only one record
    BEGIN IF (@pcCount = 1 AND @ftCount = 1)
        RETURN 1
    END

    RETURN 0
END

Then just add it to your table:

CONSTRAINT [CK_BusinessPostalCodeFees_MatchingBusinessIdPostalCodeAndProfileFeeType] 
CHECK (dbo.MatchingBusinessIdPostalCodeAndProfileFeeType(
    BusinessId, 
    BusinessPostalCodeId, 
    BusinessProfileFeeTypeId) = 1)

Ensure foreign key of a foreign key matches a base foreign key

2 Answers2