Title: SQL only one cell from column can have X value
Tags: mysql;sql
Question: Is there a way to ensure, when you design a table - SQL, MySQL -, that only one cell from column X is true, the rest being automatically false? When you change the value of a different cell to true, all other cells should automatically become false. A radio button type of exclusivity, but for a column.
mainOfferName | value
---------------------
pizza         | false
spaghetti     | false
salad         | true
stew          | false
Upon change of pizza as the main offer you'd get something like this:
mainOfferName | value
---------------------
pizza         | true
spaghetti     | false
salad         | false
stew          | false
I can run the query manually and make sure only one value is true at any given time, but I'm curious if I could put a constrain into my table so that I only change the the row/cell I need to true and the DB engine takes care of keeping only that value true.
Comment: @McAdam331 Curiosity.

Comment: What would you need this for? I feel like this goes against what you'd use a database for. Perhaps you can create a variable `selectedItem` that points to the row you wanT?

Comment: Fair enough haha. I wasn't advising against it, just curious myself.

Here is the accepted answer: Have a separate table with one row that contains the mainOfferName of the row that you want.  Then query as:
select yt.*, (nt.mainOfferName is not null) as valueFlag
from yourtable yt left join
newtable nt
on yt.mainOfferName = nt.mainOfferName;
When you want to change the value, change the value in newtable.
Note:  you can put the query into a view and read-only users will just work.
And, you could implement this with an update trigger on yourtable.  Instead of updating yourtable, it would update newtable.
Comment for this answer: That's great! Bravo ...!
Comment for this answer: have a question. what is mean of `yt.*` ? `yt` is a alias name, but what is `.*` ? when you use `*`, it is mean all and you don't need to use anything else. can you explain me more ? tnx
Comment for this answer: I have a question irrelevant to the topic, Is there a difference between these two words? `nt.mainOfferName is not null` and `nt.mainOfferName <>''`
Comment for this answer: Thanks for answered and I am ashamed to ask again, But really I like to know. I think `NULL` and empty string are the same, is it not ? Can you show me a example that be `NULL` but not empty string ? Or vice versa.
Comment for this answer: Sounds interesting. Thank you.
Comment for this answer: @stack the asterisk is used to select all columns. In this case, there is a join so the asterisk will select all columns from both tables. By putting the table alias first, only the columns from that table will be selected.
Comment for this answer: @stack . . . Yes.  The first checks that the column is not `NULL`.  The second checks that the column is neither `NULL` nor the empty string.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:06/22/15', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><sql>', 'Title': 'SQL only one cell from column can have X value', 'ViewCount': 370, 'id': 30987429, 'user_AboutMe': '', 'user_AccountId': 1686272.0, 'user_DisplayName': 'Radu Andrei', 'user_Reputation': 1065.0, 'user_Views': 90.0, 'user_id': 1549496.0}}