Constraint for only one record marked as default

Question

How could I set a constraint on a table so that only one of the records has its isDefault bit field set to 1?

The constraint is not table scope, but one default per set of rows, specified by a FormID.

Answer 1

Use a unique filtered index

On SQL Server 2008 or higher you can simply use a unique filtered index

CREATE UNIQUE INDEX IX_TableName_FormID_isDefault
    ON TableName(FormID)
    WHERE isDefault = 1

Where the table is

CREATE TABLE TableName(
    FormID INT NOT NULL,
    isDefault BIT NOT NULL
)

For example if you try to insert many rows with the same FormID and isDefault set to 1 you will have this error:

Cannot insert duplicate key row in object 'dbo.TableName' with unique index 'IX_TableName_FormID_isDefault'. The duplicate key value is (1).

Source: http://technet.microsoft.com/en-us/library/cc280372.aspx

Answer 2

The question implies to me that you have a primary table that has some child records and one of those child records will be the default record. Using address and a separate default table here is an example of how to make that happen using third normal form. Of course I don't know if it's valuable to answer something that is so old but it struck my fancy.

--drop table dev.defaultAddress;
--drop table dev.addresses;
--drop table dev.people;

CREATE TABLE [dev].[people](
    [Id] [int] identity primary key,
    name char(20)
)
GO

CREATE TABLE [dev].[Addresses](
    id int identity primary key,
    peopleId int foreign key references dev.people(id),
    address varchar(100)
) ON [PRIMARY]

GO
CREATE TABLE [dev].[defaultAddress](
    id int identity primary key,
    peopleId int foreign key references dev.people(id),
    addressesId int foreign key references dev.addresses(id))
go
create unique index defaultAddress on dev.defaultAddress (peopleId)
go
create unique index idx_addr_id_person on dev.addresses(peopleid,id);
go
ALTER TABLE dev.defaultAddress
   ADD CONSTRAINT FK_Def_People_Address
   FOREIGN KEY(peopleID, addressesID)
   REFERENCES dev.Addresses(peopleId, id)
go
insert into dev.people (name) 
    select 'Bill' union 
    select 'John' union 
    select 'Harry'
insert into dev.Addresses (peopleid, address) 
    select 1, '123 someplace' union 
    select 1,'work place' union
    select 2,'home address' union
    select 3,'some address'
insert into dev.defaultaddress (peopleId, addressesid)
    select 1,1 union
    select 2,3 
-- so two home addresses are default now
-- try adding another default address to Bill and you get an error
select * from dev.people 
    join dev.addresses on people.id = addresses.peopleid
    left join dev.defaultAddress on defaultAddress.peopleid = people.id and defaultaddress.addressesid = addresses.id 
insert into dev.defaultaddress (peopleId, addressesId)
    select 1,2 

GO

Answer 3

I don't know about SQLServer.But if it supports Function-Based Indexes like in Oracle, I hope this can be translated, if not, sorry.

You can do an index like this on suposed that default value is 1234, the column is DEFAULT_COLUMN and ID_COLUMN is the primary key:

CREATE 
UNIQUE 
 INDEX only_one_default 
    ON my_table
     ( DECODE(DEFAULT_COLUMN, 1234, -1, ID_COLUMN) )

This DDL creates an unique index indexing -1 if the value of DEFAULT_COLUMN is 1234 and ID_COLUMN in any other case. Then, if two columns have DEFAULT_COLUMN value, it raises an exception.

Answer 4

Here's a modification of Damien_The_Unbeliever's solution that allows one default per FormID.

CREATE VIEW form_defaults
AS
SELECT FormID
FROM whatever
WHERE isDefault = 1
GO
CREATE UNIQUE CLUSTERED INDEX ix_form_defaults on form_defaults (FormID)
GO

But the serious relational folks will tell you this information should just be in another table.

CREATE TABLE form
FormID int NOT NULL PRIMARY KEY
DefaultWhateverID int FOREIGN KEY REFERENCES Whatever(ID)

Answer 5

@Andy Jones gave an answer above closest to mine, but bearing in mind the Rule of Three, I placed the logic directly in the stored proc that updates this table. This was my simple solution. If I need to update the table from elsewhere, I will move the logic to a trigger. The one default rule applies to each set of records specified by a FormID and a ConfigID:

ALTER proc [dbo].[cpForm_UpdateLinkedReport]
    @reportLinkId int,
    @defaultYN bit,
    @linkName nvarchar(150)
as
if @defaultYN = 1
begin
    declare @formId int, @configId int
    select @formId = FormID, @configId = ConfigID from csReportLink where ReportLinkID = @reportLinkId
    update csReportLink set DefaultYN = 0 where isnull(ConfigID,  @configId) = @configId and FormID = @formId
end
update
    csReportLink
 set
    DefaultYN = @defaultYN,
    LinkName = @linkName
where
    ReportLinkID = @reportLinkId

Answer 6

CREATE VIEW vOnlyOneDefault
AS
  SELECT 1 as Lock
  FROM <underlying table>
  WHERE Default = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_vOnlyOneDefault on vOnlyOneDefault (Lock)
GO

You'll need to have the right ANSI settings turned on for this.

Answer 7

This is a fairly complex process that cannot be handled through a simple constraint.

We do this through a trigger. However before you write the trigger you need to be able to answer several things:

do we want to fail the insert if a default exists, change it to 0 instead of 1 or change the existing default to 0 and leave this one as 1? what do we want to do if the default record is deleted and other non default records are still there? Do we make one the default, if so how do we determine which one?

You will also need to be very, very careful to make the trigger handle multiple row processing. For instance a client might decide that all of the records of a particular type should be the default. You wouldn't change a million records one at a time, so this trigger needs to be able to handle that. It also needs to handle that without looping or the use of a cursor (you really don't want the type of transaction discussed above to take hours locking up the table the whole time).

You also need a very extensive tesing scenario for this trigger before it goes live. You need to test: adding a record with no default and it is the first record for that customer adding a record with a default and it is the first record for that customer adding a record with no default and it is the not the first record for that customer adding a record with a default and it is the not the first record for that customer Updating a record to have the default when no other record has it (assuming you don't require one record to always be set as the deafault) Updating a record to remove the default Deleting the record with the deafult Deleting a record without the default Performing a mass insert with multiple situations in the data including two records which both have isdefault set to 1 and all of the situations tested when running individual record inserts Performing a mass update with multiple situations in the data including two records which both have isdefault set to 1 and all of the situations tested when running individual record updates Performing a mass delete with multiple situations in the data including two records which both have isdefault set to 1 and all of the situations tested when running individual record deletes

Answer 8

From a normalization perspective, this would be an inefficient way of storing a single fact.

I would opt to hold this information at a higher level, by storing (in a different table) a foreign key to the identifier of the row which is considered to be the default.

CREATE TABLE [dbo].[Foo](
    [Id] [int] NOT NULL,
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DefaultSettings](
    [DefaultFoo] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DefaultSettings]  WITH CHECK ADD  CONSTRAINT [FK_DefaultSettings_Foo] FOREIGN KEY([DefaultFoo])
REFERENCES [dbo].[Foo] ([Id])
GO

ALTER TABLE [dbo].[DefaultSettings] CHECK CONSTRAINT [FK_DefaultSettings_Foo]
GO

Answer 9

You could use an insert/update trigger.

Within the trigger after an insert or update, if the count of rows with isDefault = 1 is more than 1, then rollback the transaction.

Answer 10

You could do it through an instead of trigger, or if you want it as a constraint create a constraint that references a function that checks for a row that has the default set to 1

EDIT oops, needs to be <=

Create table mytable(id1 int, defaultX bit not null default(0))
go

create Function dbo.fx_DefaultExists()
returns int as 
Begin
    Declare @Ret int
    Set @ret = 0
    Select @ret = count(1) from mytable 
    Where defaultX = 1

    Return @ret
End
GO
Alter table mytable add
CONSTRAINT  [CHK_DEFAULT_SET] CHECK 
 (([dbo].fx_DefaultExists()<=(1)))
GO
Insert into mytable (id1, defaultX) values (1,1)

Insert into mytable (id1, defaultX) values (2,1)