Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
"I want to guarantee that this query will always return exactly one row" -- always? What about when PostalCodes is empty? If a row already has a the property should, it be prevented from being set to false unless another row (if one exists) is set to true within the same SQL statement? Can zero rows have the property between transaction boundaries? Should the last row in the table be forced to have the property and be prevented from being deleted? Experience tells me that "guarantee exactly one row" tends to mean something different in reality, often simply "at most one row". –  onedaywhen Mar 29 '12 at 13:37
add comment

5 Answers

up vote 7 down vote accepted

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:

share|improve this answer
    
gbn - looks like the filtered index/DRI solution is SQL 2008 only so looks like I will skip to trying option 2. –  emaynard Aug 19 '11 at 9:22
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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