How to Create a "Unique" Constraint on a Boolean MySQL Column

How to create a unique constraint on a boolean MySQL column?

I think this is not the best way to model the situation of a single default value.

Instead, I would leave the IsDefault column out and create a separate table with one row and only the column(s) that make(s) up the primary key of your main table. In this table you place the PK value(s) that identify the default record.

This results in considerably less storage and avoids the update issue of temporarily not having a default value (or, alternatively, temporarily having two default values) when you update.

You have numerous options for ensuring that there is one-and-only-one row in the default table.

mysql constraint unique when boolean field is true

You can make use of unique keys ignoring null (which is following the sql standard): any tuple containing null is not equivalent to any tuple. That means, that in a unique index (country_id, main), you can have the row (1, null) as often as you want, but at most one row (1, 1) and one row (1, 0).

You would need to allow null for main, and would need to use 1 and null (not 0) to encode if it is an expert or not (otherwise you can only have one non-expert). The constraint would then be:

main boolean null default null,
constraint unique index uidx_expert_country (country_id, main)

This might require changes to your application, might look sketchy (as false is obviously not the same as null) and might soften up the interpretation of your column (as it now can contain 3 values null, 0/false and 1/true although you can prevent that with an additional foreign key to a table with a single row containing 1).

So if you do not want to do that, you can define an additional column that will be calculated in a trigger or as a generated column, and then define a unique index on that "dummy"-column:

main boolean  not null default false,
main_unq  boolean as (if(main = true,true, null)) stored,
constraint unique index uidx_expert_country (country_id, main_unq)

For a different way to encode the information which expert is the main expert of a country, you could e.g. add a column main_expert_id to your country-table (and a foreign key to expert_country to verify that the combination exists) and remove the column main.

Can I add a constraint to multiple boolean columns so that at least one must be true?

Simply check if any of them are true in a check constraint:

alter table tablename   
add constraint const_name check (paymentType1 or paymentType2 or paymentType3);

How to give a unique constraint to a combination of a column and a fixed value in Oracle?

Slight variation on MarmiteBomber's approach, to avoid concatenating the values (which could cause accidental clashes with non-integer values):

create table t (a number, b number, c varchar2(5),
  constraint t_chk check (c in ('true', 'false'))
);

create unique index t_unq
on t (case when c = 'true' then a end, case when c = 'true' then b end);

insert into t(a,b,c) values (1,2,'true');

1 row inserted.

insert into t(a,b,c) values (1,2,'false');

1 row inserted.

insert into t(a,b,c) values (1,2,'false');

1 row inserted.

insert into t(a,b,c) values (1,2,'true');

ORA-00001: unique constraint (MY_SCHEMA.T_UNQ) violated

select * from t;

         A          B C    
---------- ---------- -----
         1          2 true 
         1          2 false
         1          2 false

---

Quick example of why non-integers (if they can exist) might be a problem:

create unique index uq_true on test(case when c = 'true' then a||'.'||b end);

insert into test(a,b,c) values (1.1, 2,'true');

1 row inserted.

insert into test(a,b,c) values (1, 1.2,'true');

ORA-00001: unique constraint (MY_SCHEMA.UQ_TRUE) violated

select * from test;

         A          B C    
---------- ---------- -----
       1.1          2 true 

... because for both '1.1' ||'.'|| '2' and '1' ||'.'|| '1.2' resolve to the same string, '1.1.2'.

This can also be a problem when combining string values rather than numbers. In either case you can avoid it by using a delimiter which cannot exist in either value; harder to do with strings, but with numbers any punctuation other than a period (or comma to be safe) would probably do - unless someone has a weird setting for nls_numeric_characters...

Unique constraint for combined columns queried with another column

You can add a unique constraint/index to do this:

create unique index unq_contacts_user_id_contact_type_is_primary
    on contact(user_id, contact_type, is_primary);

Note: this only works if is_primary takes on the values "1" or NULL. This is very important. Otherwise, you will be limited to at most one "not primary" contact as well as one primary one.

In Postgres (or Oracle), you would use a filtered index:

create unique index unqf_contacts_user_id_contact_type
    on contact(user_id, contact_type)
    where is_primary = 1; -- or however you specify that it is true

How to enforce uniqueness with boolean column (kinda like radio buttons)?

Probably unique filtered index is the thing that may help you:

create table AlbumPics (
    AlbumID int not NULL,
    PictureID int not NULL,
    IsCover bit not NULL,
    primary key clustered (AlbumID, PictureID)
)

create unique index UX_AlbumPics_CoverPic
    on AlbumPics (AlbumID) where IsCover = 1

-- insert 3 pics for album 1, one of them is cover pic
insert into AlbumPics values
(1, 1, 0), (1, 2, 1), (1, 3, 0)

-- try to insert one more cover for album 1 - will fail
insert into AlbumPics values
(1, 4, 1)

Bool field as a unique index

Essentially a bool-value in sql (or bit in mssql) is an very short integer. So basically there's no reason that it would not be allowed as foreign key.
But from a design perspective it does not make any sense.

Mysql Unique constraint over multiple columns

The problem is solved: nothing wrong with my solution, but I had to change my spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect to spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

---

Related Topics