sql server ユニーク制約 null も許可する一意の制約を作成するにはどうすればよいですか。




unique not null (12)

私は、GUIDを設定する列に一意の制約を設定したいと考えています。 ただし、私のデータには、この列のnull値が含まれています。 複数のNULL値を許可する制約を作成するにはどうすればよいですか?

ここでは例のシナリオです。 このスキーマを考えてみましょう:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
  LibraryCardId UNIQUEIDENTIFIER NULL,
  CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)

私が達成しようとしていることについては、このコードを参照してください:

-- This works fine:
INSERT INTO People (Name, LibraryCardId) 
 VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');

-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId) 
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Richard Roe', NULL);

-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marcus Roe', NULL);

最後のステートメントは失敗し、次のメッセージが表示されます。

UNIQUE KEY制約 'UQ_People_LibraryCardId'の違反。 オブジェクト 'dbo.People'に重複キーを挿入できません。

実際のデータの一意性をチェックしながら複数のNULL値を許可するように、スキーマや一意制約を変更するにはどうすればよいですか?


クラスタ化インデックス付きビューで一意の制約を作成することは可能です

次のようにビューを作成することができます:

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

このようなユニークな制約があります。

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE 
  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

このコードでは、あなたがテキストボックスを使って登録フォームを作成し、insertとurを使用すると、textBoxは空で、submitボタンをクリックします。

CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]
ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;

SQL Server 2008およびそれ以降

一意のインデックスをフィルタリングするだけです。

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

下位バージョンでは、マテリアライズド・ビューはまだ必要ありません

SQL Server 2005以前のバージョンでは、表示なしで実行できます。 私はちょうどあなたが私のテーブルの一つを求めているようなユニークな制約を加えました。 SamAccountName列に一意性が必要だが、複数のNULLを許可するには、マテリアライズド・ビューではなくマテリアライズド・カラムを使用した。

ALTER TABLE dbo.Party ADD SamAccountNameUnique
   AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
   UNIQUE (SamAccountNameUnique)

実際に必要な一意の列がNULLの場合、テーブル全体で一意になることが保証される計算された列に何かを置くだけで済みます。 この場合、 PartyIDはID列であり、数値はSamAccountNameと決して一致しないので、私のために働いた。 独自の方法を試すことができます。実際のデータとの交差の可能性がないように、データのドメインを理解していることを確認してください。 これは次のような差別化文字を前置するのと同じくらいシンプルになります。

Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))

PartyIDがいつか非数値になり、 SamAccountNameと一致する可能性があっても、今は問題ではありません。

計算された列を含む索引が存在すると、暗黙的に各式の結果がディスクに保管され、他のデータが表に保管されることに注意してください。

索引を必要としない場合は、列式定義の最後にキーワードPERSISTEDを追加して、式をディスクにあらかじめ計算しておくことで、CPUを節約できます。

可能であれば、SQL Server 2008以降では、フィルタリングされたソリューションを使用してください。

論争

データベースの専門家の中には、これが「サロゲートNULL」のケースと見なされていることに注意してください。問題には間違いがあります( 実際に何かが欠落しているかどうかを判断しようとする問題や、 NULL以外のサロゲート値の数には狂気のように乗算されます)。

しかし、私はこのケースが異なると信じています。 追加しようとしている計算列は決して何も決定するために使われることはありません。 それはそれ自身の意味を持たず、適切に定義された他の列では別に見つけられていない情報をコード化しません。 それは決して選択または使用すべきではありません。

だから、私の話は、これは代理NULLではないということです。私はそれに固執しています! UNIQUEインデックスをトリックしてNULLを無視する以外の目的でNULL以外の値を実際には使用しないため、通常の代理NULL作成で発生する問題はありません。

つまり、インデックス付きビューを使用しても問題はありませんが、 SCHEMABINDINGを使用するという要件など、いくつかの問題があります。 ベーステーブルに新しい列を追加することをお楽しみください(少なくともインデックスを削除してからビューを削除するか、ビューをスキーマにバインドしないように変更する必要があります)。 SQL Server(2005) (またはそれ以降のバージョン)、 (2000) インデックス付きビューを作成するための要件の完全なリストを参照してください。

更新

列が数値の場合、 Coalesceを使用する一意制約が衝突を起こさないようにするという課題があります。 その場合、いくつかのオプションがあります。 陰性の範囲にのみ「サロゲートNULL」を、陰性の範囲に「実際の値」を入れるために、負の数を使用することがあります。 あるいは、次のパターンを使用することができます。 表IssueIssueIDPRIMARY KEY )ではIssueIDがある場合とない場合がありますが、 TicketIDがある場合は一意でなければなりません。

ALTER TABLE dbo.Issue ADD TicketUnique
   AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
   UNIQUE (TicketID, TicketUnique);

IssueID 1にチケット123がある場合、 UNIQUE制約は値(123、NULL)になります。 IssueID 2にチケットがない場合は、オンになります(NULL、2)。 いくつかの考えでは、この制約はテーブル内のどのローでも複製できず、複数のNULLを許容していることが示されます。


前述のとおり、SQL ServerはUNIQUE CONSTRAINTについてはANSI標準を実装していません。 2007年以来、 Microsoft Connectにチケットがあります 。ここで提案されているように、今日のベスト・オプションは、 別の回答または計算列に記載されているフィルタリングされたインデックスを使用することです。

CREATE TABLE [Orders] (
  [OrderId] INT IDENTITY(1,1) NOT NULL,
  [TrackingId] varchar(11) NULL,
  ...
  [ComputedUniqueTrackingId] AS (
      CASE WHEN [TrackingId] IS NULL
      THEN '#' + cast([OrderId] as varchar(12))
      ELSE [TrackingId_Unique] END
  ),
  CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])
)

たぶん " INSTEAD OF "トリガーを考え、自分でチェックしてみてください。 ルックアップを有効にするために、列にクラスタ化されていない(一意でない)インデックスがある。


INSTEAD OFトリガーを作成して特定の条件とエラーが満たされているかどうかを確認できます。 索引を作成すると、大きな表にコストがかかる可能性があります。

ここに例があります:

CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony
 INSTEAD OF INSERT, UPDATE
 AS
BEGIN
 IF EXISTS(
    SELECT TOP (1) 1 
    FROM inserted i
    GROUP BY i.pony_name
    HAVING COUNT(1) > 1     
    ) 
     OR EXISTS(
    SELECT TOP (1) 1 
    FROM PONY.tbl_pony t
    INNER JOIN inserted i
    ON i.pony_name = t.pony_name
    )
    THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;
 ELSE
    INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)
    SELECT pony_name, stable_id, pet_human_id
    FROM inserted
 END

それはデザイナーでも行うことができます

このウィンドウを表示するには、「索引> プロパティー」を右クリックします。


あなたが探しているのは確かに、ANSI規格SQL:92、SQL:1999、SQL:2003の一部です。つまり、UNIQUE制約は重複しているNULL以外の値を許可せず、複数のNULL値を受け入れる必要があります。

しかし、SQL ServerのMicrosoftの世界では、単一のNULLは許可されていますが、複数のNULLは許されません。

SQL Server 2008では、NULLを除外する述語に基づいて一意のフィルタ済みインデックスを定義できます。

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

以前のバージョンでは、制約を適用するためにNOT NULL述語を使用してVIEWSを使用することができます。


以下のユニークなインデックスを適用したとき:

CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull
ON employee(badgeid)
WHERE badgeid IS NOT NULL;

すべての非ヌル更新と挿入が以下のエラーで失敗しました:

次のSETオプションの設定が正しくないため、UPDATEが失敗しました: 'ARITHABORT'。

私はこれをMSDN見つけました

計算列または索引ビューで索引を作成または変更する場合は、SET ARITHABORTをONにする必要があります。 SET ARITHABORTがOFFの場合、計算列または索引付きビューの索引を持つ表のCREATE、UPDATE、INSERT、およびDELETE文は失敗します。

ですから、これを正しく動作させるために私はこれを行いました

[Database] - > Properties - > Options - > Other Options - > Misscellaneous - > Arithmetic Abort Enabled - > trueを右クリックします。

私は、このオプションをコードで設定することは可能であると信じています

ALTER DATABASE "DBNAME" SET ARITHABORT ON

しかし、私はこれをテストしていない


SQL Server 2008 +

WHERE句を使用して複数のNULLを受け入れる一意索引を作成できます。 以下の答えを見てください。

SQL Server 2008より前

UNIQUE制約を作成してNULLを許可することはできません。 NEWID()のデフォルト値を設定する必要があります。

UNIQUE制約を作成する前に、既存の値をNULLのNEWID()に更新します。


NULL列のみを選択し、ビューにUNIQUE INDEXを作成するビューを作成します。

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

テーブルの代わりにビューでINSERTUPDATEを実行する必要があることに注意してください。

あなたはINSTEAD OFトリガーでそれを行うことができます:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL) 
WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
MAXDOP = 0) ON [PRIMARY];






tsql