I've explored similar use case with a "foreign key" to multiple parents. (Can't add links here on hashnode yet - have to wait a month, so go to my kibeha dot dk site and look for post "Assertions for data integrity on a less-than-ideal data model". Sorry for inconvenience...)
Near the end of that post, my "Attempt 4" works the same way as your "assertion flight_aircraft_one_source".
But I have another method in "Attempt 5" where I create individual assertions for each parent by doing a filter in the ALL clause.
I think that method ought to perform better when the database validates the assertion, but not certain.
However, that method does require that there's a way in the child table to know in which parent it should look for the key. My use case has this, yours does not. So you cannot use my method 🙁.
Nice use case, Plamen 👍.
I've explored similar use case with a "foreign key" to multiple parents. (Can't add links here on hashnode yet - have to wait a month, so go to my kibeha dot dk site and look for post "Assertions for data integrity on a less-than-ideal data model". Sorry for inconvenience...) Near the end of that post, my "Attempt 4" works the same way as your "assertion flight_aircraft_one_source".
But I have another method in "Attempt 5" where I create individual assertions for each parent by doing a filter in the ALL clause. I think that method ought to perform better when the database validates the assertion, but not certain.
However, that method does require that there's a way in the child table to know in which parent it should look for the key. My use case has this, yours does not. So you cannot use my method 🙁.
Cheerio /Kim