Xjyuk

Why is the battery jumpered to a resistor in this schematic?

What allows us to use imaginary numbers?

Set theory with antielements?

When did Bilbo and Frodo learn that Gandalf was a Maia?

Eric Andre had a dream

Adding things to bunches of things vs multiplication

6502: is BCD *fundamentally* the same performance as non-BCD?

Why don't modern jet engines use forced exhaust mixing?

A Magic Diamond

How do I answer an interview question about how to handle a hard deadline I won't be able to meet?

Can anyone help me what's wrong here as i can prove 0 = 1?

How to train a replacement without them knowing?

Meaning of だけはわからない

Why does "auf der Strecke bleiben" mean "to fall by the wayside"?

What should I do if actually I found a serious flaw in someone's PhD thesis and an article derived from that PhD thesis?

Good way to stop electrolyte tabs from turning into powder?

Would molten tin solidify and coat an organic horn?

What's the point of writing that I know will never be used or read?

Ghost house where the house only appeared once a year for it was the ghost

Is nullptr falsy?

Select elements of a list by comparing it to another list

Doesn't the speed of light limit imply the same electron can be annihilated twice?

How do I pass a "list of lists" as the argument to a function of the form F[x,y]?

What is the hottest thing in the universe?

How to have a “kinda” unique column

MySQL unique constraint across two columnsMySQL - unique constraints designAccept null value on parent column in multipart relationshipIs it possible to enforce a Unique Constraint on a Dataset comprised of joinsMySQL unique index only if column has specific valuemysql UNIQUE KEY multiple columns with one column as NULLDoes LIMIT 1 still have effect on a unique columnLeft join on a varchar columnHow to model the relationships (a) between properties and owners and (b) between properties and tenants?Add foreign key constraint to an existing table column, referencing a column in a newly created table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

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?

mysql constraint unique-constraint

share|improve this question

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

New contributor

Nathaniel Pisarski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  An Account has only one Owner. But can one Owner have multiple Accounts?
  
  – Rick James
  7 hours ago
  

  
  
  
  

add a comment | 

1

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?

mysql constraint unique-constraint

share|improve this question

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

New contributor

Nathaniel Pisarski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  An Account has only one Owner. But can one Owner have multiple Accounts?
  
  – Rick James
  7 hours ago
  

  
  
  
  

add a comment | 

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?

mysql constraint unique-constraint

share|improve this question

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

New contributor

Nathaniel Pisarski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

New contributor

Nathaniel Pisarski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

New contributor

Nathaniel Pisarski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

New contributor

Nathaniel Pisarski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

Nathaniel PisarskiNathaniel Pisarski

10844 bronze badges

2 Answers
2

active

oldest

votes

2

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.

share|improve this answer

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  7 hours ago
  

  
  
  
  

add a comment | 

2

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).

share|improve this answer

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 / null seems weird. I think that your answer is the best you can get without faffing about in the schema though.
  
  – Nathaniel Pisarski
  7 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Nathaniel Pisarski is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f245535%2fhow-to-have-a-kinda-unique-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

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.

share|improve this answer

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  7 hours ago
  

  
  
  
  

add a comment | 

2

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.

share|improve this answer

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  7 hours ago
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

share|improve this answer

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

answered 7 hours ago

Willem RenzemaWillem Renzema

1,37611 gold badge77 silver badges88 bronze badges

2

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).

share|improve this answer

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 / null seems weird. I think that your answer is the best you can get without faffing about in the schema though.
  
  – Nathaniel Pisarski
  7 hours ago
  

  
  
  
  

add a comment | 

2

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).

share|improve this answer

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 / null seems weird. I think that your answer is the best you can get without faffing about in the schema though.
  
  – Nathaniel Pisarski
  7 hours ago
  

  
  
  
  

add a comment | 

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).

share|improve this answer

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges

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)
)

share|improve this answer

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges

answered 8 hours ago

mustacciomustaccio

11.5k99 gold badges2828 silver badges4444 bronze badges