How to implement a 'default' flag that can only be set on a single row

Question

For example, with a table similar to this:

create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));

It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I just want to be able to enforce the constraint that it can only be set on a single row.

Answer 1

SQL Server 2008 - Filtered unique index

CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'

Answer 2

SQL Server 2000, 2005:

You can take advantage of the fact that only one null is allowed in a unique index:

create table t( id int identity, 
                chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), 
                chk2 as case chk1 when 'Y' then null else id end );
create unique index u_chk on t(chk2);

for 2000, you may need SET ARITHABORT ON (thanks to @gbn for this info)

Answer 3

Oracle:

Since Oracle doesn't index entries where all indexed columns are null, you can use a function-based unique index:

create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N')));
create unique index idx on foo(case when chk='Y' then 'Y' end);

This index will only ever index a single row at most.

Knowing this index fact, you can also implement the bit column slightly differently:

create table foo(bar integer, chk char(1) check (chk ='Y') UNIQUE);

Here the possible values for the column chk will be Y and NULL. Only one row at most can have the value Y.

Answer 4

I think this is a case of structuring your database tables correctly. To make it more concrete, if you have a person with multiple addresses and you want one to be the default, I think you should store the addressID of the default address in the person table, not have a default column in the address table:

Person
-------
PersonID
Name
etc.
DefaultAddressID (fk to addressID)

Address
--------
AddressID
Street
City, State, Zip, etc.

You can make the DefaultAddressID nullable, but this way the structure enforces your constraint.

Answer 5

MySQL:

create table foo(bar serial, chk boolean unique);
insert into foo(chk) values(null);
insert into foo(chk) values(null);
insert into foo(chk) values(false);
insert into foo(chk) values(true);

select * from foo;
+-----+------+
| bar | chk  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
|   3 |    0 |
|   4 |    1 |
+-----+------+

insert into foo(chk) values(true);
ERROR 1062 (23000): Duplicate entry '1' for key 2
insert into foo(chk) values(false);
ERROR 1062 (23000): Duplicate entry '0' for key 2

Check constraints are ignored in MySQL so we have to consider null or false as false and true as true. At most 1 row can have chk=true

You may consider it an improvement to add a trigger to change false into true on insert/update as a workaround for the lack of a check constraint - IMO it isn't an improvement though.

I hoped to be able to use a char(0) because it

is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and ''

Unfortunately, with MyISAM and InnoDB at least, I get

ERROR 1167 (42000): The used storage engine can't index column 'chk'

--edit

this is not a good solution after all since on MySQL, boolean is a synonym for tinyint(1), and so allows non-null values than 0 or 1. It is possible that bit would be a better choice

Answer 6

PostgreSQL:

create table foo(bar serial, chk char(1) unique check(chk='Y'));
insert into foo default values;
insert into foo default values;
insert into foo(chk) values('Y');

select * from foo;
 bar | chk
-----+-----
   1 |
   2 |
   3 | Y

insert into foo(chk) values('Y');
ERROR:  duplicate key value violates unique constraint "foo_chk_key"

--edit

or (much better), use a unique partial index:

create table foo(bar serial, chk boolean not null default false);
create unique index foo_i on foo(chk) where chk;
insert into foo default values;
insert into foo default values;
insert into foo(chk) values(true);

select * from foo;
 bar | chk
-----+-----
   1 | f
   2 | f
   3 | t
(3 rows)

insert into foo(chk) values(true);
ERROR:  duplicate key value violates unique constraint "foo_i"

Answer 7

SQL Server:

How to do it:

1. The best way is a filtered index. Uses DRI
   SQL Server 2008+

2. Computed column with uniqueness. Uses DRI
   See Jack Douglas' answer. SQL Server 2005 and before

3. An indexed/materialised view which is like a filtered index. Uses DRI
   All versions.

4. Trigger. Uses Code, not DRI.
   All versions

How not to do it:

1. Check constraint with a UDF. This isn't safe for concurrency and snapshot isolation.
   See One Two Three Four

Answer 8

This kind of problem is another reason why I asked this quiestion:

Application Settings in Database

If you have an application setting table in your database you could have an entry that would reference the ID of the one record you want to be considered 'special'. Then you would just look-up what the ID is from your settings table, in this way you dont need an entire column for just one item being set.

Answer 9

Possible approaches using widely implemented technologies:

1) Revoke 'writer' privileges on the table. Create CRUD procedures that ensure the constraint is enforced at transaction boundaries.

2) 6NF: drop the CHAR(1) column. Add a referencing table constrained to ensure its cardinality cannot exceed one:

alter table foo ADD UNIQUE (bar);

create table foo_Y
(
 x CHAR(1) DEFAULT 'x' NOT NULL UNIQUE CHECK (x = 'x'), 
 bar int references foo (bar)
);

Change the application semantics so that the considered 'default' is the row in the new table. Possibly use views to encapsulate this logic.

3) Drop the CHAR(1) column. Add a seq integer column. Put a unique constraint on seq. Change the application semantics so that the considered 'default' is the row where the seq value is one or the seq value the largest/smallest value or similar. Possibly use views to encapsulate this logic.

Answer 10

For those who use MySQL, here is an appropriate Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS SetDefaultForZip;
CREATE PROCEDURE SetDefaultForZip (NEWID INT)
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;

    SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
    IF FOUND_TRUE = 1 THEN
        SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
        IF NEWID <> OLDID THEN
            UPDATE PostalCode SET isDefault = FALSE WHERE ID = OLDID;
            UPDATE PostalCode SET isDefault = TRUE  WHERE ID = NEWID;
        END IF;
    ELSE
        UPDATE PostalCode SET isDefault = TRUE WHERE ID = NEWID;
    END IF;
END;
$$
DELIMITER ;

To make sure your table is clean and the stored procedure is working, assuming ID 200 is the default, run these steps:

ALTER TABLE PostalCode DROP INDEX isDefault_ndx;
UPDATE PostalCodes SET isDefault = FALSE;
ALTER TABLE PostalCode ADD INDEX isDefault_ndx (isDefault);
CALL SetDefaultForZip(200);
SELECT ID FROM PostalCodes WHERE isDefault = TRUE;

Here is a Trigger that help as well:

DELIMITER $$
CREATE TRIGGER postalcodes_bu BEFORE UPDATE ON PostalCodes FOR EACH ROW
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;
    IF NEW.isDefault = TRUE THEN
        SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
        IF FOUND_TRUE = 1 THEN
            SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
            UPDATE PostalCodes SET isDefault = FALSE WHERE ID = OLDID;
        END IF;
    END IF;
END;
$$
DELIMITER ;

To make sure your table is clean and the trigger is working, assuming ID 200 is the default, run these steps:

DROP TRIGGER postalcodes_bu;
ALTER TABLE PostalCode DROP INDEX isDefault_ndx;
UPDATE PostalCodes SET isDefault = FALSE;
ALTER TABLE PostalCode ADD INDEX isDefault_ndx (isDefault);
DELIMITER $$
CREATE TRIGGER postalcodes_bu BEFORE UPDATE ON PostalCodes FOR EACH ROW
BEGIN
    DECLARE FOUND_TRUE,OLDID INT;
    IF NEW.isDefault = TRUE THEN
        SELECT COUNT(1) INTO FOUND_TRUE FROM PostalCode WHERE isDefault = TRUE;
        IF FOUND_TRUE = 1 THEN
            SELECT ID INTO OLDID FROM PostalCode WHERE isDefault = TRUE;
            UPDATE PostalCodes SET isDefault = FALSE WHERE ID = OLDID;
        END IF;
    END IF;
END;
$$
DELIMITER ;
UPDATE PostalCodes SET isDefault = TRUE WHERE ID = 200;
SELECT ID FROM PostalCodes WHERE isDefault = TRUE;

Give it a Try !!!

Answer 11

In SQL Server 2000 and over you can use Indexed Views to implement complex (or multi-table) constraints like the one you're asking for.
Also Oracle has a similar implementation for materialized views with deferred check constraints.

See my post here.

Answer 12

Standard Transitional SQL-92, widely implemented e.g. SQL Server 2000 and above:

Revoke 'writer' privileges from the table. Create two views for WHERE chk = 'Y' and WHERE chk = 'N' respectively, including WITH CHECK OPTION. For the WHERE chk = 'Y' view, include a search condition to the effect that its cardinality cannot exceed one. Grant 'writer' privileges on the views.

Example code for the views:

CREATE VIEW foo_chk_N
AS
SELECT *
  FROM foo AS f1
 WHERE chk = 'N' 
WITH CHECK OPTION

CREATE VIEW foo_chk_Y
AS
SELECT *
  FROM foo AS f1
 WHERE chk = 'Y' 
       AND 1 >= (
                 SELECT COUNT(*)
                   FROM foo AS f2
                  WHERE f2.chk = 'Y'
                )
WITH CHECK OPTION

Answer 13

Here's a solution for MySQL and MariaDB using virtual columns that's a bit more elegant. It requires MySQL >= 5.7.6 or MariaDB >= 5.2:

MariaDB [db]> create table foo(bar varchar(255), chk boolean);

MariaDB [db]> describe foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| bar   | varchar(255) | YES  |     | NULL    |       |
| chk   | tinyint(1)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Create a virtual column that is NULL if you do not want to enforce the Unique contrraint:

MariaDB [db]> ALTER table foo ADD checked_bar varchar(255) as (IF(chk, bar, null)) PERSISTENT UNIQUE;

(For MySQL, use STORED instead of PERSISTENT.)

MariaDB [db]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.01 sec)

MariaDB [salt_dev]> insert into foo(bar, chk) values('a', false);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', true);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> insert into foo(bar, chk) values('a', true);
ERROR 1062 (23000): Duplicate entry 'a' for key 'checked_bar'

MariaDB [db]> insert into foo(bar, chk) values('b', true);
Query OK, 1 row affected (0.00 sec)

MariaDB [db]> select * from foo;
+------+------+-------------+
| bar  | chk  | checked_bar |
+------+------+-------------+
| a    |    0 | NULL        |
| a    |    0 | NULL        |
| a    |    0 | NULL        |
| a    |    1 | a           |
| b    |    1 | b           |
+------+------+-------------+

Answer 14

Standard FULL SQL-92: use a subquery in a CHECK constraint, not widely implemented e.g. supported in Access2000 (ACE2007, Jet 4.0, whatever) and above when in ANSI-92 Query Mode.

Example code: note CHECK constraints in Access are always table level. Because the CREATE TABLE statement in the question uses a row-level CHECK constraint, it needs to be amended slightly by adding a comma:

create table foo(bar int identity, chk char(1), check (chk in('Y', 'N')));

ALTER TABLE foo ADD 
   CHECK (1 >= (
                SELECT COUNT(*) 
                  FROM foo AS f2 
                 WHERE f2.chk = 'Y'
               ));

Answer 15

I only skimmed through the answers, so I might have missed a similar answer. The idea is to use a generated column that is either the p.k or a constant that does not exist as a value for the p.k.

create table foo 
(  bar int not null primary key
,  chk char(1) check (chk in('Y', 'N'))
,  some_name generated always as ( case when chk = 'N' 
                                        then bar 
                                        else -1 
                                   end )
, unique (somename)
);

AFAIK this is valid in SQL2003 (since you where looking for an agnostic solution). DB2 allows it, not sure how many other vendors that accepts it.

Answer 16

SQLite:

CREATE TABLE foo (
  name TEXT NOT NULL PRIMARY KEY,
  chk BOOLEAN NOT NULL DEFAULT false
);

CREATE TRIGGER IF NOT EXISTS foo_chk_insert BEFORE INSERT ON foo
BEGIN
  SELECT CASE
    WHEN
      ((SELECT chk FROM foo WHERE chk = true) = NEW.chk)
    THEN
      RAISE (FAIL, "chk true already exists")
  END;
END;

CREATE TRIGGER IF NOT EXISTS foo_chk_update BEFORE UPDATE OF chk ON foo
BEGIN
  SELECT CASE
    WHEN
      ((SELECT chk FROM foo WHERE chk = true) = NEW.chk)
    THEN
      RAISE (FAIL, "chk true already exists")
  END;
END;

And then

insert into foo values
  ("hola", false),
  ("que tal", true),
  ("como te va", false);
Query OK, 3 rows affected


select * from foo;
+------------+-----+
| name       | chk |
+------------+-----+
| hola       | 0   |
| que tal    | 1   |
| como te va | 0   |
+------------+-----+

insert into foo values ("que hay", true);
chk true already exists

Although, this doesn't let you update the already default row!!!

update foo set chk = true, name = "que tal!" where name = "que tal";
chk true already exists

So you need to do this WHEN clause instead:

(OLD.chk != true AND (SELECT chk FROM foo WHERE chk = true) = NEW.chk)

and now:

update foo set chk = true, name = "que tal!" where name = "que tal";
Query OK, 1 row affected