Mysql – How to have a “kinda” unique column

constraintMySQLunique-constraint

How can I have a MySQL database number column that only allows one 1, but infinite 0's? Some type of constraint or something.

Elaboration

To clarify what I mean and why I want this:

Imagine you have a MySQL table (let's say "accounts"). An account can be "assigned" to multiple people, but only one person can "own" it. This is in a way similar to bank accounts or Netflex.

So, the schema might look like

Accounts
[id] [name]

Account_membership
[account_id] [user_id] [is_owner]

Here's the rub: You can only have one owner. But, You can have infinite non_owners who are members. How can I ensure that this is the case?

A unique constraint won't work for this, because (1, 1, 1), (1, 2, 0), (1, 3, 0) are valid rows.

So, is there some way I can accomplish this with mysql?

Best Answer

Create a separate table named account_owner with columns account_id and user_id.

Have account_id be the primary key and both account_id and user_id should reference their respective parent tables. Since only one record can be entered per account_id then you ensure there is always at most one owner.

Alternatively, just add a owner_user_id column in the Accounts table if you want to enforce exactly 1 owner at all times.

Related Question