ProfK ProfK - 4 months ago 8
SQL Question

Constraint for only one record marked as default

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

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)
Comments