Home Database Mysql Tutorial SQL Server允许重复空字段不空值解决方法(1/2)

SQL Server允许重复空字段不空值解决方法(1/2)

Jun 07, 2016 pm 05:48 PM
nbsp null tb test

解决方案1:
对于这个问题,大家的第一个想法可能是:在caption这个字段上面加一个唯一键不就可以了吗?好,我们按着这个思路做下去,先创建唯一索引。
 代码如下:
create unique nonclustered index un_test_tb
on test_tb(caption)
go

索引创建好了,我们来测试下效果
 代码如下:
insert into test_tb (caption)
values (null)
go
insert into test_tb (caption)
values (null)
go

运行之后我们会收到下面的错误信息:
以下为引用的内容:
消息 2601,级别 14,状态 1,第 1 行
不能在具有唯一索引 'un_test_tb' 的对象 'dbo.test_tb' 中插入重复键的行。
语句已终止。
所以该解决方案是不行的。
解决方案2:
添加约束,让sql server在插入数据的时候,先验证下已有数据中是否有现在要插入的这个值。由于这个约束不是简单的一个运算,因此我们先创建一个函数,然后再在约束中调用这个函数。
创建验证逻辑函数:
 代码如下:
create function [dbo].[fn_ck_test_tb_caption]()
returns bit
as
begin
if(exists(
select 1
from test_tb as a
where (caption is not null) and exists
(select 1 as expr1
from test_tb
where (caption is not null) and (caption = a.caption) and (a.testid testid))
))
return 0
return 1
end
go

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Huawei Watch GT 5 smartwatch gets update with new features Huawei Watch GT 5 smartwatch gets update with new features Oct 03, 2024 am 06:25 AM

Huawei is rolling out software version 5.0.0.100(C00M01) for the Watch GT 5 and the Watch GT 5 Prosmartwatchesglobally. These two smartwatches recently launched in Europe, with the standard model arriving as the company’s cheapest model. This Harmony

Tekken\'s Colonel Sanders dream fried by KFC Tekken\'s Colonel Sanders dream fried by KFC Oct 02, 2024 am 06:07 AM

Katsuhiro Harada, the Tekken series director, once seriously tried to bring Colonel Sanders into the iconic fighting game. In an interview with TheGamer, Harada revealed that he pitched the idea to KFC Japan, hoping to add the fast-food legend as a g

First look: Leaked unboxing video of upcoming Anker Zolo 4-port 140W wall charger with display First look: Leaked unboxing video of upcoming Anker Zolo 4-port 140W wall charger with display Oct 01, 2024 am 06:32 AM

Earlier in September 2024, Anker's Zolo 140W charger was leaked, and it was a big deal since it was the first-ever wall charger with a display from the company. Now, a new unboxing video from Xiao Li TV on YouTube gives us a first-hand look at the hi

Samsung Galaxy Z Fold Special Edition revealed to land in late October as conflicting name emerges Samsung Galaxy Z Fold Special Edition revealed to land in late October as conflicting name emerges Oct 01, 2024 am 06:21 AM

The launch of Samsung's long-awaited 'Special Edition' foldable has taken another twist. In recent weeks, rumours about the so-called Galaxy Z Fold Special Edition went rather quiet. Instead, the focus has shifted to the Galaxy S25 series, including

New Xiaomi Mijia Graphene Oil Heater with HyperOS arrives New Xiaomi Mijia Graphene Oil Heater with HyperOS arrives Oct 02, 2024 pm 09:02 PM

Xiaomi will shortly launch the Mijia Graphene Oil Heater in China. The company recently ran a successful crowdfunding campaign for the smart home product, hosted on its Youpin platform. According to the page, the device has already started to ship to

Garmin releases Adventure Racing activity improvements for multiple smartwatches via new update Garmin releases Adventure Racing activity improvements for multiple smartwatches via new update Oct 01, 2024 am 06:40 AM

Garmin is ending the month with a new set of stable updates for its latest high-end smartwatches. To recap, the company released System Software 11.64 to combat high battery drain across the Enduro 3, Fenix E and Fenix 8 (curr. $1,099.99 on Amazon).

Cybertruck FSD reviews praise quick lane switching and full-screen visualizations Cybertruck FSD reviews praise quick lane switching and full-screen visualizations Oct 01, 2024 am 06:16 AM

Tesla is rolling out the latest Full Self-Driving (Supervised) version 12.5.5 and with it comes the promised Cybertruck FSD option at long last, ten months after the pickup went on sale with the feature included in the Foundation Series trim price. F

Manjaro 24.1 \'Xahea\' launches with KDE Plasma 6.1.5, VirtualBox 7.1, and more Manjaro 24.1 \'Xahea\' launches with KDE Plasma 6.1.5, VirtualBox 7.1, and more Oct 02, 2024 am 06:06 AM

With a history of over one decade, Manjaro is regarded as one of the most user-friendly Linux distros suitable for both beginners and power users, being easy to install and use. Mostly developed in Austria, Germany, and France, this Arch-based distro

See all articles