Constraint across tables

Using T-SQL in an Azure SQL Database, I have two tables. Call them Foo and Fum. In order to represent consistent real-world data, each record in Foo must have at least one associated record in Fum (it may have more than one). Fum records carry a foreign key linking them to the associated Foo. Is there a way to set up some kind of constraint or something else to insure that every Foo has at least one Fum? What would be the best practice for this? Thank you for any pointers.

There are ways and means to enforce such a thing on a static structure using triggers, check constraints or indexed views but it's more problematic on a changing structure.

Consider what happens when you add a new entry, for example. The Fum row has a FK constraint to the Foo row, so the Foo row needs to be inserted first. However, if you could enforce your data constraint, inserting that row would violate your data model since it wouldn't have a corresponding Fum row.

The "best" solutions for this kind of situation are somewhat subjective. Sometimes it's best just to enforce it via application rules. If the data is only updated periodically, it may be possible to temporarily disable constraints during update. Other times a report that highlights "invalid" records can suffice.

2 Likes