SQL Server允許重複空欄位不空欄位值唯一

來源:互聯網
上載者:User

今天同學向我提了一個問題,我覺得蠻有意思,現記錄下來大家探討下。問題是:在一個表裡面,有一個允許為空白的欄位,空是可以重複的,但是不為空白的值需要唯一。

表結構如下面代碼建立

以下為引用的內容:

CREATE TABLE test_tb
(
    TestId int not null identity(1,1) primary key,
    Caption nvarchar(100) null
);
GO

解決方案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

在約束中引用函數:

以下為引用的內容:

ALTER TABLE test_tb
ADD CONSTRAINT CK_test_tb_Caption CHECK (dbo.fn_CK_test_tb_Caption() = 1)
GO

現在來測試下效果。先來測試NULL值

以下為引用的內容:

INSERT INTO test_tb (Caption)
VALUES (null)
GO
INSERT INTO test_tb (Caption)
VALUES (null)
GO
SELECT * FROM test_tb
GO

可以成功運行,而且也出了多行為NULL的情況。現在再來測試不為空白的插入情況。

以下為引用的內容:

INSERT INTO test_tb (Caption)
VALUES (N'AAA')
GO
INSERT INTO test_tb (Caption)
VALUES (N'BBB')
GO
INSERT INTO test_tb (Caption)
VALUES (N'BBB')
GO
SELECT * FROM test_tb
GO

結果是在第三條語句的時候報錯了,表中的Caption欄位也有‘AAA’和‘BBB’了,這也正好是我們要的結果。

所以解決方案2是正確的。但是為了這麼一個小小功能,就寫這麼長一段東西是不是太繁瑣了呢?我們來看下面的解決方案。

解決方案3:(只適用於SQL Server 2008)

SQL Server 2008中有了一個優雅的解決方案,那就是篩選索引。篩選索引是一種經過最佳化的非叢集索引,尤其適用於涵蓋從定義完善的資料子集中選擇資料的查詢。篩選索引使用篩選謂詞對錶中的部分行進行索引。有了篩選索引,我們只需要寫一條語句就達到上面的效果。

以下為引用的內容:

CREATE UNIQUE NONCLUSTERED INDEX un_test_tb 
ON test_tb(Caption)
WHERE Caption is not null
GO

再用上面的一些測試語句來測試的話,會發現完全是達到了我們的要求。這個方案的唯一缺點就是該語句只有SQL Server 2008支援。不知道各位有沒有又優雅又適用於各個版本的SQL Server的解決方案,望不勝賜教。(來源:部落格園)



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。