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.

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.

share|improve this question
    
inspired by this question that is limited to MySQL –  Jack Douglas Aug 19 '11 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. –  Mike Sherrill 'Cat Recall' Oct 6 '11 at 8:57

13 Answers 13

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)

share|improve this answer

SQL Server 2008 - Filtered unique index

CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'
share|improve this answer

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

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

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

share|improve this answer
3  
Is there no DRI based solution for MySQL? Only code? I'm curious because I'm starting to use MySQL more and more... –  gbn Aug 19 '11 at 14:01

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

share|improve this answer
    
This could answer my comment to RolandoMySQLDBA's answer: can we have MySQL solutions with DRI? –  gbn Aug 19 '11 at 14:44
    
It's a bit ugly though because of the null, false, true - I do wonder if there is something neater... –  Jack Douglas Aug 19 '11 at 14:47
    
@Jack - +1 for nice try at pure DRI approach in MySQL. –  RolandoMySQLDBA Aug 19 '11 at 16:07

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.

share|improve this answer
    
chk needs a not null constraint? –  Jack Douglas Aug 19 '11 at 14:50
    
@jack: You can add a not null constraint if you don't want nulls (It was not clear to me from the question specs). Only one row can have the value 'Y' in any case. –  Vincent Malgrat Aug 19 '11 at 14:54
    
+1 I see what you mean - you're right it isn't necessary (but perhaps is a bit neater, especially if combined with a default)? –  Jack Douglas Aug 19 '11 at 14:57
    
Since Oracle doesn't support the boolean datatype to store data, I agree a column with Y/N would need a not null constraint to restrict the possible values to only 2 :) –  Vincent Malgrat Aug 19 '11 at 15:01
2  
@jack: your remark made me realize that an even simpler possibility is available if you accept that the column can be either Y or null, see my update. –  Vincent Malgrat Aug 19 '11 at 15:11

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.

share|improve this answer

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.

share|improve this answer
    
Could you provide a bit more "meat" in this answer, like a short snippet of code? Right now it's just a couple of general ideas and a link. –  Nick Chammas Oct 19 '11 at 14:45
    
It would be a bit difficult to fit an example here. If you click the link you will find the "meat" you're looking for. –  spaghettidba Dec 9 '11 at 8:33

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.

share|improve this answer
    
This is a great suggestion: It's more in keeping with normalized design, works with any database platform, and is easiest to implement. –  Nick Chammas Oct 1 '11 at 1:44
    
+1 but note that "an entire column" might not use any physical space depending on your RDBMS :) –  Jack Douglas Oct 19 '11 at 9:24

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
share|improve this answer
    
even if your RDBMS supports this, it will serialize like crazy so if you have more than one user, you may have a problem –  Jack Douglas Oct 19 '11 at 9:19
    
@JackDouglas: I'm not sure I understand what you mean by "serialize like crazy". Can you give more detail please? –  onedaywhen Oct 19 '11 at 10:29
    
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). –  Jack Douglas Oct 19 '11 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 '11 at 12:12
    
true enough :-) –  Jack Douglas Oct 19 '11 at 12:20

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'
               ));
share|improve this answer
1  
not good in any RDBMS I've used... caveats abound –  Jack Douglas Oct 19 '11 at 9:16

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.

share|improve this answer

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.