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

解決済

質問者：noname#86170
質問日時：2009/02/27 10:42
回答数：3件

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

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のように，枝番を付ける場合が多いです）

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

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (3件)

ベストアンサー優先
最新から表示
回答順に表示

No.1ベストアンサー

回答者： jamshid6
回答日時：2009/02/27 13:29

ユニーク制約で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 …

- 0
- 件

通報する

この回答へのお礼

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