Preventing possibility of conflicting foreign keys

Question

I am using SQL Server through EF Core 5 with an existing database. For the sake of this question, I have three tables: Customers, Assets, Loans. A Loan is made to a Customer and is based off the value of an Asset, which belongs to a Customer.

Customers have a one to many relationship with Loans. Customers have a one to many relationship with Assets. Assets have a one to many relationship with Loans

In the Loans table, I currently have a foreign key to Customers (to track who is responsible for the Loan) and a foreign key to Assets (to track which asset this Loan was made against).

I'm trying to find the best way to prevent conflicting foreign keys from being created in the Loans table (i.e., a record with one Customer, but then a Asset that is associated with a different Customer). I can do this at a code level, but I would prefer to have it done at the database level so that any changes to the code won't accidentally affect this integrity check.

I was thinking about deleting the Customer foreign key from Loans since I can get the Customer through the Assets foreign key. But I am worried about performance, and the most common query is to get a list of all Loans associated with a Customer and I can do that without needing the Assets table by filtering all Loans with a certain CustomerID.

Is there a way to make sure that an Asset with a Customer that is different than the Customer in the Loan table is not allowed at the database level?

Or should I not worry about the performance issue, and get rid of the Customer foreign key in the Loans table?

Answer 1

You can, but it doesn't mean you should.

First thing I see,

Assets have a one to many relationship with Loans

In real life, things aren't that simple; a lender might require more security for the loan, and the borrower will have to provide additional collateral to secure it. That turns the relationship into M:M, and you will need an additional table, let's call it LoanCollaterals, which will implement this link.

Something like this:

Now, you can migrate the CustomerId column along both LoanCollaterals's foreign keys, using the approach demonstrated in the answer from @seanb. This will create a schema-level constraint that ensures the asset always belongs to the customer who is taking the loan. However, I would strongly recommend against this approach:

1. It makes impossible for multiple borrowers to share the same asset. You will have to create a new Asset record for the same collateral, but this time "belonging" to another customer. Bankers won't be happy when they will hear it.
2. Most people freak out at the sight of multi-column foreign keys, and are unable to comprehend the concept.
3. Scale-up: for a school project this approach might hold, but any real world system of any complexity worth mentioning will soon become bloated with these redundant columns. I've seen a DWH model once which had 5 levels of keys migrated this way. Not pretty.
4. More links result in a more tightly-coupled entities. Reworks / expansions of such model will be considerably more challenging in the future.

In short, these kinds of checks / constraints are what stored procedures are for. You can perform all necessary validations inside the procedure, within a controlled transaction, and either create a record or throw an error to the client app, if anything doesn't satisfy the requirements. Of course, you can implement an equivalent functionality using EF, but it won't be as efficient, as it would require a client-side transaction and several app-to-DB roundtrips to perform all these business logic checks.

Answer 2

In Loans, the foreign key constraint for Customers could be pointed to Assets instead (to both AssetID and CustomerID). As there is a foreign key in Assets to Customers, the FKs should hold in enforcing correct data.

Note that FKs do not need to point to PKs - however they do need to point to unique records - hence the Unique constraint in Assets I've put below.

The below structure and approach assumes loans do not have to have an Asset backing. If it does, add a NOT NULL to the AssetID column in Loans table.

CREATE TABLE Customers (CustomerID int PRIMARY KEY);
CREATE TABLE Assets (AssetID int PRIMARY KEY, CustomerID int NOT NULL);
CREATE TABLE Loans (LoanID int PRIMARY KEY, CustomerID int NOT NULL, AssetID int);

-- Standard FKs you already have
ALTER TABLE Assets
ADD CONSTRAINT FK_Assets_Customers FOREIGN KEY (CustomerID) 
REFERENCES Customers(CustomerID);

ALTER TABLE Loans
ADD CONSTRAINT FK_Loans_Customers FOREIGN KEY (CustomerID) 
REFERENCES Customers(CustomerID);

-- Enforces a unique combination of CustomerID and AssetID in Assets 
-- (need unique constraint for FK to point to)
ALTER TABLE Assets 
ADD CONSTRAINT UC_Assets_CA UNIQUE (CustomerID, AssetID);

-- FK which enforces combination of CustomerID and AssetID is valid.
ALTER TABLE Loans
ADD CONSTRAINT FK_Loans_Assets FOREIGN KEY (CustomerID, AssetID) 
REFERENCES Assets(CustomerID, AssetID);

For demonstration purposes, here are some queries

-- These work
INSERT INTO Customers (CustomerID) VALUES (100);
INSERT INTO Customers (CustomerID) VALUES (101);
INSERT INTO Assets (AssetID, CustomerID) VALUES (200, 100);
INSERT INTO Assets (AssetID, CustomerID) VALUES (201, 101);
INSERT INTO Loans (LoanID, CustomerID, AssetID) VALUES (300, 100, 200);

-- This also works - where loan doesn't have an asset
-- (see notes above if you do not want this to happen - add a NOT NULL
-- to table definition)
INSERT INTO Loans (LoanID, CustomerID) VALUES (400, 100);

-- These don't work
INSERT INTO Loans (LoanID, CustomerID, AssetID) VALUES (500, 100, 1);
INSERT INTO Loans (LoanID, CustomerID) VALUES (600, 1);

-- Importantly, for your question - the following does NOT work 
-- because the Customer and Assets don't match
INSERT INTO Loans (LoanID, CustomerID, AssetID) VALUES (700, 101, 200);
-- ... however this one does work (assets/customers are valid)
INSERT INTO Loans (LoanID, CustomerID, AssetID) VALUES (700, 101, 201);