33

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.

2
  • inspired by this question that is limited to MySQL Aug 19, 2011 at 12:44
  • 2
    The way the question is worded suggests that using a table must be the wrong answer. But sometimes (most times?) adding another table is a good idea. And adding a table is completely database agnostic. Oct 6, 2011 at 8:57

16 Answers 16

32

SQL Server 2008 - Filtered unique index

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

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)

0
14

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.

0
13

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.

0
12

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

2
  • I would advise avoiding the use of false here since the unique constraint would only allow one such false value to be supplied. If null represents false then it should be used consistently throughout - the avoidance of false could be enforced if additional validation is available (e.g. JSR-303 / hibernate-validator). Jan 14, 2015 at 10:00
  • 1
    Recent versions of MySQL / MariaDB implement virtual columns which I believe allow a slightly more elegant solution outlined below at dba.stackexchange.com/a/144847/94908 Jul 25, 2016 at 16:26
11

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"
0
10

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
0
6

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.

0
6

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.

0
5

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

0
4

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.

0
3

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
3
  • even if your RDBMS supports this, it will serialize like crazy so if you have more than one user, you may have a problem Oct 19, 2011 at 9:19
  • if multiple users are modifying simultaneously they will have to stand in line (serialize) - sometimes this is ok, often it is not (think heavy OLTP or long transactions). Oct 19, 2011 at 11:15
  • 3
    Thanks for clarifying. I must say if multiple users are frequently setting the sole default row then the design choice (flag column in the same table) is questionable.
    – onedaywhen
    Oct 19, 2011 at 12:12
3

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           |
+------+------+-------------+
1

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'
               ));
1
0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.