You can enforce that the column is non-null and has a valid value id in the relation table. You can also enforce that your motorcycle and auto tables are referenced in the relation table or make it a view of all the ids across the types (conceptually anyway, I'd have to look into the perf). It's a contrived example but what is the issue? What are the drawbacks? These are pretty easy constraints to add.

If you drop a row from the motorcycle table without CASCADE does it error if the vehicles table still contains a pointer? If you drop it with CASCADE does it also drop it from the vehicles table?

Honestly it’s been several years since I’ve done standard app development, so I’ve forgotten some of the specific problems that I would run into. I just remember that I tried all of the gimmicks to emulate sum types and they were always clumsy. Not a very satisfying rebuttal, I know.