Null値を無視してユニークにしたい

質問

下記のようなテーブルがあります。

ID　　aaa　　bbb
10　　A01
11
12　　　　　　B01
13　　A02
14　　A03　　B02

・IDは，主キー（レコードの登録時に，IDENTITYで自動的に取得）
・aaa，bbbは，任意のタイミングで入力や編集があるが，ユニーク性を保ちたい
・aaa，bbbの入力に関しては，下記のようなテーブルを作って自動採番にしたい
Category　　Last_No
　　A　　　　　03
　　B　　　　　02

(1)データベース側にて，aaa，bbbにユニークインデックスを設定すると，
Null値に関しても重複違反になってしまったのですが，
これは，そういうものなのでしょうか？

(2)ユニークインデックスが設定できない場合に，
Null値以外の値が，ユニークである事を保つためには，
どうすれば良いのでしょうか？
自動採番だけでなく，編集も有り得るため，悩んでいます。
（編集は，A01-1のように，枝番を付ける場合が多いです）

以上，どなたかご教示願います。

回答No.1

jamshid6 · ベストアンサー

ユニーク制約でNULLを除くことができないのは、リンクページに書かれている通りです。
「PRIMARY KEY 制約とは異なり、UNIQUE 制約は NULL 値を許容できます。ただし、UNIQUE 制約が適用される他の値と同様に、NULL 値も 1 列に 1 つしか使用できません。 」

代替案はありますが、導入可否はテーブルデザインのガイドラインとレスポンス要求の程度により判断してください。
具体的には「スカラーファンクションを１つ作成して、チェック制約で使う」というものです。

CREATE FUNCTION [dbo].[ISUNIQUE](@aaa varchar(5))
RETURNS int
AS
BEGIN
IF (SELECT COUNT(*) FROM TABLE1 WHERE aaa=@aaa)>1 RETURN 0
RETURN 1
END

ALTER TABLE TABLE1 ADD CONSTRAINT [CK_aaa] CHECK (dbo.ISUNIQUE(aaa)=1)

こんな感じです。
ユニークフィールドが複合であっても、上記の応用で実現はできます。
（チェック制約に使われた関数は、チェック制約を外さない限りDROPできなくなります）

今回のはいけますが、そもそもチェック制約というのはDELETEのときには働かないことは認識しておいてください。
またここまで踏み込めなければ同じことをトリガやるのもありかと思います（トリガは権限があればオフれるのでそこが違いでしょう）。

参考URL：

http://msdn.microsoft.com/ja-jp/library/ms191166(SQL.90).aspx

お礼

jamshid6様
昨日の質問に引き続き，ご回答いただきましてありがとうございます。

ご回答いただいた内容を試し，aaaフィールドに
重複するレコードが登録出来ないことを確認しました。

ですが，これを下記のように利用しようとしたところで，
躓いてしまいました。
1回目の実行で，aaa＝A04のレコードが正常に登録された後，
2回目の実行で，制約違反で発生するエラーをトラップしたいのですが，
「INSERTステートメントはCOLUMN CHECKで，制約'CK_aaa'と矛盾しています。」
とのエラー表示となってしまい，処理が中断してしまいます。
クエリアナライザで，
　　insert TABLE1 values('A04','B03')
　　print @@error
として，@@errorにて547と返ってくる事を確認しているのですが，
どこが間違っているのでしょうか？

●SQL SERVER 2000のストアド
CREATE PROCEDURE Strd_Test_Add (
@aaa varchar(5),
@bbb varchar(5),
@rowcount int output,
@err int output
)
AS
set nocount on

begin
BEGIN TRANSACTION
insert TABLE1 (aaa,bbb) values(@aaa,@bbb)
select @rowcount=@@rowcount ,@err=@@error

if @err<>0 goto err
COMMIT TRANSACTION
end
return

err:
ROLLBACK TRANSACTION
return
GO

●Excel2003のVBA
Private Sub btn1_Click()
Set dbCN = New ADODB.Connection
dbCN.Open "Provider=SQLOLEDB;" _
& "Data Source=*****;" _
& "Initial Catalog=DB_Test;" _
& "User ID=sa;Password="

Set dbCOM = New ADODB.Command
dbCOM.ActiveConnection = dbCN
dbCOM.CommandType = adCmdStoredProc
dbCOM.CommandText = "Strd_Test_Add"
dbCOM.Parameters.Refresh
dbCOM.Parameters("@aaa") = "A04"
dbCOM.Parameters("@bbb") = "B03"
dbCOM.Execute

MsgBox dbCOM.Parameters("@rowcount").Value _
& vbCrLf & dbCOM.Parameters("@err").Value

Set dbCOM = Nothing
End Sub

回答No.3

jamshid6

SQL Server 2000だったのですね。

私の認識する限りでは、SQL Server 2005のようにエラーを抑え込むTRY/CATCH構文のようなものは2000にはないので、VBAサイドでOn Error Resume Nextを使ってエラーを抑え込むことになると思います。

お礼

jamshid6様　度々の回答，ありがとうございます。

On Error Resume Nextにてエラーを回避することにより
VBA側でエラー番号を取得できました。

回答No.2

chukenkenkou

>Null値に関しても重複違反になってしまった

SQL Serverの独自仕様になっています。

お礼

chukenkenkou様　ありがとうございます。