How to have a "kinda" unique column
In MySQL multi-column unique constraints are implemented such that they allow multiple null values. You can make use of this by using nulls to represent the non-owners:

create table account_membership (
  account_id int not null references accounts (id),
  user_id int not null references users (id),
  is_owner boolean null check (is_owner in (1, null)),
  unique key (account_id, is_owner)
)


The above will allow multiple null values in is_owner for each account_id, but only one 1.

Note that this won't be portable as other databases treat nulls in unique constraints differently (i.e. only allow one null value).