当前位置：文江博客话题详情

字段值必须唯一，除非为 NULL

发布于 2024-07-10 08:01:03 字数 240 浏览 5 评论 0原文

我使用的是 SQL Server 2005。

我有一个必须包含唯一值或 NULL 值的字段。我认为我应该使用 CHECK CONSTRAINT 或 TRIGGER for INSERT, UPDATE 来强制执行此操作。

与触发器相比，在这里使用约束是否有优势（反之亦然）？这样的约束/触发器是什么样的？

或者还有其他我没有考虑过的更合适的选择？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南渊 2024-07-17 08:01:03

我创建一个带有索引的视图，该索引通过 where 子句忽略空值...即，如果您将空值插入表中，视图不会关心，但如果您插入非空值，视图将强制执行约束。

create view dbo.UniqueAssetTag with schemabinding
as
select asset_tag
from dbo.equipment
where asset_tag is not null

GO

create unique clustered index ix_UniqueAssetTag
on UniqueAssetTag(asset_tag)

GO

所以现在我的设备表有一个 asset_tag 列，它允许多个空值，但只允许唯一的非空值。

笔记：
如果使用 mssql 2000，则需要在任何插入之前“SET ARITHABORT ON”，对表进行更新或删除。很确定这在 mssql 2005 及更高版本上不是必需的。

I create a view with the an index that ignores the nulls through the where clause...i.e. if you insert null into the table the view doesn't care but if you insert a non null value the view will enforce the constraint.

create view dbo.UniqueAssetTag with schemabinding
as
select asset_tag
from dbo.equipment
where asset_tag is not null

GO

create unique clustered index ix_UniqueAssetTag
on UniqueAssetTag(asset_tag)

GO

So now my equipment table has an asset_tag column that allows multiple nulls but only unique non null values.

Note:
If using mssql 2000, you'll need to "SET ARITHABORT ON" right before any insert, update or delete is performed on the table. Pretty sure this is not required on mssql 2005 and up.

回复收藏 0 原文

楠木可依 2024-07-17 08:01:03

这是一种带有约束的替代方法。为了强制执行此约束，您需要一个函数来计算字段值出现的次数。在您的约束中，只需确保该最大值为 1。

约束：

   field is null or dbo.fn_count_maximum_of_field(field) < 2

编辑我现在不记得 - 也无法检查它 - 约束检查是在插入/更新之前还是之后完成。我想在插入/更新失败后回滚。如果事实证明我错了，上面的 2 应该是 1。

表函数返回一个 int 并使用以下 select 来派生它

   declare @retVal int

   select @retVal = max(occurrences)
   from ( 
        select field, count(*) as occurrences
        from dbo.tbl
        where field = @field
        group by field
   ) tmp

如果您的列作为（非唯一）索引，这应该相当快在上面。

Here is an alternative way to do it with a constraint. In order to enforce this constraint you'll need a function that counts the number of occurrences of the field value. In your constraint, simply make sure this maximum is 1.

Constraint:

   field is null or dbo.fn_count_maximum_of_field(field) < 2

EDIT I can't remember right now -- and can't check it either -- whether the constraint check is done before the insert/update or after. I think after with the insert/update being rolled back on failure. If it turns out I'm wrong, the 2 above should be a 1.

Table function returns an int and uses the following select to derive it

   declare @retVal int

   select @retVal = max(occurrences)
   from ( 
        select field, count(*) as occurrences
        from dbo.tbl
        where field = @field
        group by field
   ) tmp

This should be reasonably fast if your column as a (non-unique) index on it.

回复收藏 0 原文

愛上了 2024-07-17 08:01:03

您可以通过创建计算列并在该列上放置唯一索引来实现此目的。

ALTER TABLE MYTABLE 
ADD COL2 AS (CASE WHEN COL1 IS NULL THEN CAST(ID AS NVARCHAR(255)) ELSE COL1 END)

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2)

假设 ID 是表的 PK，COL1 是“唯一或空”列。

如果您的“唯一”列为空，则计算列 (COL2) 将使用 PK 的值。

在下面的示例中，ID 列和 COL1 之间仍然有可能发生冲突：

ID     COL1    COL2
1     [NULL]    1
2        1      1

为了解决这个问题，我通常创建另一个计算列来存储 COL2 中的值来自 ID 列还是 COL1 列：

 ALTER TABLE MYTABLE 
 ADD COL3 AS (CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END)

应该更改索引to：

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2, COL3)

现在索引位于计算列 COL2 和 COL3 上，因此没有问题：

ID     COL1    COL2   COL3
1     [NULL]    1       1
2        1      1       0

You can accomplish this by creating a computed column and put the unique index on that column.

ALTER TABLE MYTABLE 
ADD COL2 AS (CASE WHEN COL1 IS NULL THEN CAST(ID AS NVARCHAR(255)) ELSE COL1 END)

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2)

This is assuming that ID is the PK of your table and COL1 is the "unique or null" column.

The computed column (COL2) will use the PK's value if your "unique" column is null.

There is still the possibility of collisions between the ID column and COL1 in the following example:

ID     COL1    COL2
1     [NULL]    1
2        1      1

To get around this I usually create another computed column which stores whether the value in COL2 comes from the ID column or the COL1 column:

 ALTER TABLE MYTABLE 
 ADD COL3 AS (CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END)

The index should be changed to:

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2, COL3)

Now the index is on both computed columns COL2 and COL3 so there is no issue:

ID     COL1    COL2   COL3
1     [NULL]    1       1
2        1      1       0

回复收藏 0 原文

撩发小公举 2024-07-17 08:01:03

在 Oracle 中，唯一键将允许多个 NULL。

在 SQL Server 2005 中，一个好的方法是通过视图进行插入，并禁用直接插入表。

这里是一些示例代码。

回复收藏 0 原文

ぽ尐不点ル 2024-07-17 08:01:03

该表上是否有主键，也许是身份列？您可以创建一个唯一键，该键是您要强制执行唯一性的字段与主键的组合。

这里有关于此类问题的讨论：http://blog.sqlauthority.com/2008/09/07/sql-server-explanation-about-usage-of-unique-index-and-unique-constraint /

仅供参考 - SQL Server 2008 引入了过滤索引，这将允许您以稍微不同的方式处理此问题。

回复收藏 0 原文

太阳公公是暖光 2024-07-17 08:01:03

通常，触发器将允许您提供比检查约束更详细和解释性的消息，因此我使用它们来避免调试中的“哪一列是坏的”游戏。

回复收藏 0 原文

小嗲 2024-07-17 08:01:03

尽管唯一约束实际上是一个索引，但约束比触发器要轻得多。

但是，唯一约束/索引中只允许有一个 NULL。
因此，您必须使用触发器来检测重复项。

MS 已请求忽略 NULLS，但 SQL 2008已过滤索引（如我键入此内容时提到的）

回复收藏 0 原文

~没有更多了~

关于作者

或十年

暂无简介

0 文章

0 评论

23 人气

关注发私信

友情链接

文江博客

字段值必须唯一，除非为 NULL

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（7）

关于作者

相关话题

热门标签

推荐作者

qq_E2Iff7

Archangel

freedog

Hunk

18819270189

wenkai

友情链接

字段值必须唯一，除非为 NULL

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（7）

关于作者

相关话题

热门标签

推荐作者

qq_E2Iff7

Archangel

freedog

Hunk

18819270189

wenkai

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。