Foreign Keys - Foes or Friend?

Question

Foreign Keys - Foes or Friend?

utsav

Old Hand

Points: 371
More actions
March 4, 2026 at 12:00 am

#4736450

Comments posted to this topic are about the item Foreign Keys - Foes or Friend?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sean redmond SSCertifiable Points: 5838 More actions · Answer 1

Our developers are experimenting with containerised databases, that is, many separate little unconnected databases rather than having the lot in one monolithic database. This means that a lot of the referential integrity work once done by SQL Server is now done by .NET and Entity Framework.

I am curious to see how this will turn out.

MMartin1 One Orange Chip Points: 27870 More actions · Answer 2

Very insightfull and easy to read. Thank you.

----------------------------------------------------

Ken McKelvey SSCoach Points: 18986 More actions · Answer 3

sean redmond wrote:

Our developers are experimenting with containerised databases, that is, many separate little unconnected databases rather than having the lot in one monolithic database. This means that a lot of the referential integrity work once done by SQL Server is now done by .NET and Entity Framework.
I am curious to see how this will turn out.

I would copy the data to a central database, maybe a data warehouse, and check the referential integrity at least once per day. The central repository will also be useful for reporting.

sean redmond SSCertifiable Points: 5838 More actions · Answer 4

That, I think, is a very good idea and I will implement it.

Ken McKelvey SSCoach Points: 18986 More actions · Answer 5

It would be interesting if you could post outline conclusions in a couple of months.

I suspect the microservices architecture is good for encapsulation but could easily get out of hand from a data perspective especially if different databases are used in each container. (eg SQL Server, MongoDB, MySQL etc.) It could also get slow and complicated if each service starts to have too many interfaces. As usual, the results will depend on the skill and discipline of the developers.

If developer time is the pinch point, then microservices might increase development speed at the expense of:

1. data people checking integrity and writing pipelines to a central repository.

2. devops people having more complicated deployments, patching and monitoring.

Sorry, I have strayed away from the topic of foreign keys.