How to have a “kinda” unique column Qqwi0evoth w caya]Yyll
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?
-
An Account has only one Owner. But can one Owner have multiple Accounts? – Rick James 8 hours ago
2 Answers
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.
-
I'm accepting this answer because it seems like it follows best practices most closely. Hopefully over time we can migrate our data model to be closer to that, because I think it makes the most sense. As it stands the table really has two "Types" of data, which obviously isn't good (and is causing this issue), Thank you – Nathaniel Pisarski 8 hours ago
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).
-
We're actually abusing this quirk in our current implementation. Although I agree it's kind of worrying that it would break on other databases (we're going to store this data in Redshift eventually, where it will break). And there's just the fact that
1/nullseems weird. I think that your answer is the best you can get without faffing about in the schema though. – Nathaniel Pisarski 8 hours ago