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

Question

I would like to add a BOOLEAN column to a MySQL table which will be named is_default. In this column, only one record can have is_default set to true.

How can I add this constraint to my column with MySQL?

Thanks!

---

UPDATE

If it is not a constraint that I should add. How are we dealing with this type of problem on DBs?

Answer 1

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.

Answer 2

You can't have such a constraint in MySQL.

However if instead of TRUE and FALSE you use the values TRUE and NULL then it will work because a UNIQUE column can have multiple NULL values. Note that this doesn't apply to all databases, but it will work in MySQL.

CREATE TABLE table1(b BOOLEAN UNIQUE);  INSERT INTO table1 (b) VALUES (TRUE);   // Succeeds INSERT INTO table1 (b) VALUES (TRUE);   // Fails: duplicate entry '1' for key 'b'  INSERT INTO table1 (b) VALUES (FALSE);  // Succeeds INSERT INTO table1 (b) VALUES (FALSE);  // Fails: duplicate entry '0' for key 'b'  INSERT INTO table1 (b) VALUES (NULL);   // Succeeds INSERT INTO table1 (b) VALUES (NULL);   // Succeeds! 

Answer 3

How are we dealing with this type of problem on DBs?

In some DBMS you can create a partial index.

In PostgreSQL this would look like this:

CREATE UNIQUE INDEX only_one_true 
  ON the_table (is_default)
  WHERE is_default

SQL Server 2008 has a very similar syntax.

On Oracle it's a bit more complicated but doable as well:

CREATE UNIQUE INDEX only_one_true 
  ON the_table (CASE 
                  WHEN is_default = 1 THEN 1
                  ELSE null
                END)

The Oracle solution might work on any DBMS that supports expression for an index definition.