仅限制一条标记为默认记录 [英] Constraint for only one record marked as default

问题描述

如何在表上设置约束，以便只有一条记录的 isDefault 位字段设置为 1?

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

约束不是表范围，而是每组行的一个默认值，由 FormID 指定.

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

推荐答案

这是 Damien_The_Unbeliever 解决方案的修改，允许每个 FormID 有一个默认值.

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)

这篇关于仅限制一条标记为默认记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！