My general recommendation here is that as a plugin author you should always add indexes in a migration to properly enforce the constraint. In fact majority of plugins we right these days avoid custom fields unless absolutely needed and prefer using custom tables which are far easier to reason about. In this specific case you want an index of:
create unique index idxStartEvent on topic_custom_fields(topic_id) where name = 'start_event'
Not sure what else really we need to do in core here, we have considered a revamp of custom fields but are somewhat worried about it. One thing I am considering is simply dropping array support from custom fields cause they are just causing enormous amounts of issues over the years.