今天同学向我提了一个问题,是SQL Server中一个关于“如何保证可空字段中非空值唯一”的问题,我觉得蛮有意思,现记录下来大家探讨下。
问题是:在一个表里面,有一个允许为空的字段,空是可以重复的,但是不为空的值需要唯一。
表结构如下面代码创建
- CREATETABLEtest_tb
- (
-
TestIdintnotnullidentity(1,1)primarykey,
-
Captionnvarchar(100)null
- );
- GO
解决方案:
解决方案1:
对于这个问题,大家的第一个想法可能是:在Caption这个字段上面加一个唯一键不就可以了吗?好,我们按着这个思路做下去,先创建唯一索引。
- CREATEUNIQUENONCLUSTEREDINDEXun_test_tb
-
ONtest_tb(Caption)
- GO
索引创建好了,我们来测试下效果
- INSERTINTOtest_tb(Caption)
-
VALUES(null)
- GO
-
INSERTINTOtest_tb(Caption)
-
VALUES(null)
- GO
运行之后我们会收到下面的错误信息:
- 消息2601,级别14,状态1,第1行
-
不能在具有唯一索引'un_test_tb'的对象'dbo.test_tb'中插入重复键的行。
- 语句已终止。
所以该解决方案是不行的。
解决方案2:
添加约束,让SQL Server在插入数据的时候,先验证下已有数据中是否有现在要插入的这个值。由于这个约束不是简单的一个运算,因此我们先创建一个函数,然后再在约束中调用这个函数。
创建验证逻辑函数:
- CREATEFUNCTION[dbo].[fn_CK_test_tb_Caption]()
-
RETURNSBIT
-
AS
-
BEGIN
- IF(EXISTS(
-
SELECT1
-
FROMtest_tbASa
-
WHERE(CaptionISNOTNULL)ANDEXISTS
-
(SELECT1ASExpr1
-
FROMtest_tb
-
WHERE(CaptionISNOTNULL)AND(Caption=a.Caption)AND(a.TestId<>TestId))
- ))
-
RETURN0
-
-
RETURN1
-
END
- GO
在约束中引用函数:
- ALTERTABLEtest_tb
-
ADDCONSTRAINTCK_test_tb_CaptionCHECK(dbo.fn_CK_test_tb_Caption()=1)
- GO
现在来测试下效果。先来测试NULL值
- INSERTINTOtest_tb(Caption)
-
VALUES(null)
- GO
-
INSERTINTOtest_tb(Caption)
-
VALUES(null)
- GO
-
SELECT*FROMtest_tb
- GO
可以成功运行,而且也出了多行为NULL的情况。现在再来测试不为空的插入情况。
- INSERTINTOtest_tb(Caption)
-
VALUES(N'AAA')
- GO
-
INSERTINTOtest_tb(Caption)
-
VALUES(N'BBB')
- GO
-
INSERTINTOtest_tb(Caption)
-
VALUES(N'BBB')
- GO
-
SELECT*FROMtest_tb
- GO
结果是在第三条语句的时候报错了,表中的Caption字段也有'AAA'和'BBB'了,这也正好是我们要的结果。
所以解决方案2是正确的。但是为了这么一个小小功能,就写这么长一段东西是不是太繁琐了呢?我们来看下面的解决方案。
解决方案3:(只适用于SQL Server 2008)
SQL Server 2008中有了一个优雅的解决方案,那就是筛选索引。筛选索引是一种经过优化的非聚集索引,尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。有了筛选索引,我们只需要写一条语句就达到上面的效果。
- CREATEUNIQUENONCLUSTEREDINDEXun_test_tb
-
ONtest_tb(Caption)
-
WHERECaptionisnotnull
- GO
再用上面的一些测试语句来测试的话,会发现完全是达到了我们的要求。
这个方案的唯一缺点就是该语句只有SQL Server 2008支持。。
不知道各位有没有又优雅又适用于各个版本的SQL Server的解决方案,望不胜赐教。
分享到:
相关推荐
数据完整性是任何数据库系统要保证的重点。不管系统计划得有多好,空数据值的问题总是存在。本文探讨了在SQL Server中处理这些值时涉及的3个问题:计数、使用空表值以及外键处理。
二、如何在sql语句中判断为NULL的时间字段 假设表为:TestTable SN DateTime1 DateTime2 1 2011-10-24 2011-10-25 2 NULL 2011-10-26 3 2011-10-25 NULL 用case进行查询,若写成: select (case DateTime1 ...
SQL Server 中的空值问题 SQL Server 中的空值问题是一个非常重要且复杂的主题。 空值的存在可以导致数据处理和查询结果的不确定性。因此,了解空值的语义、关系模型和三值逻辑在 SQL Server 中的应用非常重要。 ...
Oracle SQL 中判断值为空或 Null 的方法有多种,在本文中,我们将介绍 Oracle 和 SQL Server 中的空值判断方法。 Oracle 中的空值判断 在 Oracle 中,可以使用 `NVL` 函数来判断值为空或 Null。`NVL` 函数的语法...
C#往sqlserver数据库中插入时间型数据时,直接插入null,数据库中会变成1900-1-1的数据,这个demo使用了参数的方法进行插入,很简单
数据完整性是任何数据库系统要保证的重点。不管系统计划得有多好,空数据值的问题总是存在。本文探讨了在SQL Server中处理这些值时涉及的3个问题:计数、使用空表值以及外键处理。
C++builder与C++builder与SQL server连接
C#+存取SqlServer中的Image类型
SQL Server 多表联接查询 SQL Server 是一个功能强大且灵活的关系数据库管理系统,支持多表联接查询,能够从多个表中检索数据,以满足复杂的业务需求。在本节中,我们将详细介绍 SQL Server 的多表联接查询,包括...
A、为标识表中唯一的实体 B、创建唯一的索引,允许空值 C、只允许以表中第一字段建立 D、允许有多个主键的 4、在Transact-SQL语法中,SELECT语句的完整语法较复杂,但至少包括的部分(1___),使用关键字(2___)...
用于PGSQL不知道数据表名称,统计这个数据库所有数据表字段为空的个数,并用临时表展现出来
代码如下: -判断某些字段是否为空 –case select case when ‘字段...null) end as ‘NewName’ –SQL Server 2005:coalesce select coalesce(‘字符串类型字段’,’\N’) as ‘NewName’ select coalesce(convert(var
主要介绍了在SQL Server中使用ISNULL执行空值判断查询,ISNULL的好处是可以直接写在SELECT查询语句中,需要的朋友可以参考下
Laravel的可空数据库字段 通常,未分配值的数据库字段默认为null 。 在创建具有外键约束的记录时,这一点尤其重要。 注意,必须将数据库字段配置为允许空值。 如果该字段未配置为允许null,则更新版本MySQL会将值...
To get a handle on which SQL Server versions are available, first you need to understand some nomenclature semantics. SQL Server 2008, which shipped in August 2008, is now into Service Pack 1 (SP1)...
3. 索引:为数据快速检索提供支持且可以保证数据唯一性的辅助数据结构。 4. 约束:用于为表中的列定义完整性的规则。 5. 默认值:为列提供的缺省值。 6. 存储过程:存放于服务器的预先编译好的一组 T-SQL 语句。 7. ...