How do I force one record to have a true value for a boolean column, and all others a false value?

Question

I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.

Basically, I want to guarantee that this query will always return exactly one row:

SELECT ID, Zip 
FROM PostalCodes 
WHERE isDefault=True

How would I do that in SQL?

Answer 1

Edit: this is geared to SQL Server before we knew MySQL

There are 4 5 ways to do this: in order of most desired to least desired

We don't know what RDBMS this if for though so not all may apply

1. The best way is a filtered index. This uses DRI to maintain uniqueness.

2. Computed column with uniqueness (see Jack Douglas' answer) (added by edit 2)

3. An indexed/materialised view which is like a filtered index using DRI

4. Trigger (as per other answers)

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

Note that the requirement for "one default" is on the table, not the stored procedure. The stored procedure will have the same concurrency problems as a check constraint with a udf

Note: asked on SO many times:

• https://stackoverflow.com/questions/4810615/sql-server-bit-column-constraint-1-row-1-all-others-0/4810654#4810654
• https://stackoverflow.com/questions/6088361/the-proper-way-to-implement-unique-constraint-that-allows-multiple-null-values-in/6088397#6088397

Answer 2

Note: This answer was given before it was clear the asker wanted something MySQL specific. This answer is biased towards SQL Server.

Apply a CHECK constraint to the table that calls a UDF and makes sure its return value is <= 1. The UDF can just count the rows in the table WHERE isDefault = TRUE. This will ensure that there is no more than 1 default row in the table.

You should add a bitmap or filtered index on the isDefault column (depending on your platforom) to make sure this UDF runs very quickly.

Caveats

• Check constraints have certain limitations. Namely, they are invoked for every row that is modified, even if those rows have not been committed yet in a transaction. So if you start a transaction, set a new row to be the default and then unset the old row, your check constraint will complain. That is because it will execute after you make the new row the default, find there are now two defaults, and fail. The solution then is to make sure you unset the default row first before setting a new default, even if you are doing this work in a transaction.
• As gbn pointed out, UDFs may return inconsistent results if you are using snapshot isolation in SQL Server. However, an inline UDF does not run into this issue, and since a UDF that just does a count is inline-able, this is not an issue here.
• The strength of a database engine's processing power is in its ability to work on sets of data at once. With a UDF-backed check constraint, the engine will be limited to applying this UDF serially to every row that is modified. In your use case it is unlikely that you will perform mass updates to the PostalCodes table, however this remains a performance concern for those situations where set-based activity is likely.

Given all these caveats, I recommend using gbn's suggestion of a filtered unique index instead of a check constraint.

Answer 3

Here is a Stored Procedure (MySQL Dialect):

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;

Instead of a Stored Procedure, how about a Trigger ?

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;

Answer 4

You could use a trigger to apply the rules. When an UPDATE or INSERT statement sets isDefault to True, the SQL in the trigger can set all other rows to False.

You'll have to consider other situations when applying this. For example, what should happen if more than one row in and UPDATE or INSERT sets isDefault to True? What rules will apply to avoid breaking the rule?

Also, is it possible the condition where there is no default? What will happen when an update sets the isDefault from True to False.

Once you define the rules, you can build them in the trigger.

Then, as indicated in another answer, you want to apply a check constraint to help enforce the rules.

Answer 5

Following sets up an example table and data:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    [id] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , [IsDefault] BIT DEFAULT 0
)
GO

INSERT dbo.MyTable DEFAULT VALUES
GO 100

If you create a stored procedure to set the IsDefault flag and remove permissions to change the base table, you could enforce this with the query below. It would require a table scan each time.

DECLARE @Id INT
SET @Id = 10

UPDATE
    dbo.MyTable
SET
    IsDefault = CASE WHEN [id] = @Id THEN 1 ELSE 0 END 
GO

Depending on the size of the table, an index on IsDefault (DESC) might result in one or both of the queries below avoiding a full scan.

DECLARE @Id INT
SET @Id = 10

UPDATE
    dbo.MyTable
SET
    IsDefault = CASE WHEN [id] = @Id THEN 1 ELSE 0 END
FROM
    dbo.MyTable
WHERE
    [id] = @Id
OR  IsDefault = 1

UPDATE
    dbo.MyTable
SET
    IsDefault = CASE WHEN [id] = @Id THEN 1 ELSE 0 END
FROM
    dbo.MyTable
WHERE
    [id] = @Id
OR  ([id] != @Id AND IsDefault = 1)

If you can't remove permissions from the base table, need to enforce integrity by other means and are using SQL2008, you could make use of a filtered unique index:

CREATE UNIQUE INDEX IX_MyTable_IsDefault  ON dbo.MyTable (IsDefault) WHERE IsDefault = 1

Answer 6

For MySQL that doesn't have filtered indexes, you could do something like the following. It exploits the fact that MySQL allows multiple NULLs in unique indexes, and uses a dedicated table and a foreign key to simulate a datatype that can only have NULL and 1 as values.

With this solution, instead of true / false, you would just use 1 / NULL.

CREATE TABLE DefaultFlag (id TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO DefaultFlag (id) VALUES (1);

CREATE TABLE PostalCodes (
    ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Zip VARCHAR (32) NOT NULL,
    isDefault TINYINT UNSIGNED NULL DEFAULT NULL,
    CONSTRAINT FOREIGN KEY (isDefault) REFERENCES DefaultFlag (id),
    UNIQUE KEY (isDefault)
);

INSERT INTO PostalCodes (Zip, isDefault) VALUES ('123', 1   );
/* Only one row can be default */
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('abc', 1   );
/* ERROR 1062 (23000): Duplicate entry '1' for key 'isDefault' */

/* MySQL allows multiple NULLs in unique indexes */
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('456', NULL);
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('789', NULL);

/* only NULL and 1 are admitted in isDefault thanks to foreign key */
INSERT INTO PostalCodes (Zip, isDefault) VALUES ('789', 2   );
/* ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`PostalCodes`, CONSTRAINT `PostalCodes_ibfk_1` FOREIGN KEY (`isDefault`) REFERENCES `DefaultFlag` (`id`))*/

The only thing that can go wrong, I think, is if someone adds a row to the DefaultFlag table. I think this is not a big concern, and you can always fix it with permissions if you really want to be safe.

Answer 7

SELECT ID, Zip FROM PostalCodes WHERE isDefault=True 

This was essentially giving you problems because you put the field in the wrong place and that's all.

Have a 'Defaults' table, with PostalCode in it, that holds the id which you are looking for. In general, it's also good to name your tables in accordance with one record, so the name of your initial table would be better off called 'PostalCode'. Defaults will be a single row table, until you need to specialise your defaults against some other configuration (such as historics).

The final query that you want is as follows:

select Zip from PostalCode p, Defaults d where p.ID=d.PostalCode;