In other words, is it possible to create a situation where there are orphaned child records, for which no parent exists anymore?
Imagine a situation where ON DELETE CASCADE cannot be used and an INSTEAD OF DELETE trigger is used on the parent table instead. If I define my IOD trigger poorly, not properly cleaning up all the child records before deleting the parent, would SQL server allow this to happen? Or would it realise that the deletion of the parent will leave an orphaned child and throw an exception anyway?
I use MS SQL Server 2016 and I am wondering if I can always blindly rely on a left join on a foreign key (properly defined as a foreign key constraint) not creating any null records in the right hand side table.
Ok after thinking about it a bit more I can answer my own question. The child table controls whether a parent record can be deleted, via the ON DELETE XY setting of the foreign key. So if I don't set ON DELETE CASCADE on the child table, and the IOD trigger on the parent table does not delete the child record before attempting to delete the parent record, the deletion of the parent record will be blocked. That is because the implicit default setting is ON THE DELETE NO ACTION
What I meant with the join example was: "Can I be sure that a corresponding parent record always exists for every child record in a foreign key relationship"