[解決済み】2つの列の組み合わせに一意制約を追加する

質問

テーブルがあるのですが、なぜか同じ人が私の Person テーブルを2回作成しました。現在、主キーは単なる自動番号ですが、他に2つのフィールドが存在し、強制的に一意にしたいのです。

例えば、そのフィールドは

ID  
Name  
Active  
PersonNumber  

PersonNumberが一意で、Active = 1のレコードが1つだけ必要です。

(2つのフィールドの組み合わせはユニークである必要があります。）

SQLサーバーの既存のテーブルで、他の人が既存の値と同じ値を挿入すると失敗するようにするには、どのような方法がありますか？

解決方法は？

重複を削除した後、「削除」ボタンをクリックしてください。

ALTER TABLE dbo.yourtablename
  ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);

または

CREATE UNIQUE INDEX uq_yourtablename
  ON dbo.yourtablename(column1, column2);

もちろん、SQL Server が行を挿入しようとして例外を返す前に、最初にこの違反をチェックしたほうがよい場合もあります (例外は高くつきます)。

• エラー処理方法の違いによるパフォーマンスへの影響

• TRY/CATCHを入力する前に制約違反の可能性があるかどうかをチェックする

アプリケーションに変更を加えることなく、例外がアプリケーションにバブリングするのを防ぎたい場合には INSTEAD OF トリガーを使用します。

CREATE TRIGGER dbo.BlockDuplicatesYourTable
 ON dbo.YourTable
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 FROM inserted AS i 
    INNER JOIN dbo.YourTable AS t
    ON i.column1 = t.column1
    AND i.column2 = t.column2
  )
  BEGIN
    INSERT dbo.YourTable(column1, column2, ...)
      SELECT column1, column2, ... FROM inserted;
  END
  ELSE
  BEGIN
    PRINT 'Did nothing.';
  END
END
GO

しかし、挿入を実行しなかったことをユーザーに伝えなければ、ユーザーはなぜデータがないのか、例外が報告されなかったのかと思うでしょう。

EDIT ここに、あなたの質問と同じ名前を使ってまで、まさにあなたが求めていることを行い、それを証明する例があります。上記のアイデアで、どちらか一方の列しか扱えないと決めつける前に、試してみるべきです。

USE tempdb;
GO

CREATE TABLE dbo.Person
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(32),
  Active BIT,
  PersonNumber INT
);
GO

ALTER TABLE dbo.Person 
  ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 0, 22);
GO

-- fails:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

全部終わった後のテーブルのデータ。

ID   Name   Active PersonNumber
---- ------ ------ ------------
1    foo    1      22
2    foo    0      22

最後の挿入時のエラーメッセージ。

Msg 2627, レベル 14, ステート 1, 行 3 UNIQUE KEY 制約 'uq_Person' に違反しています。オブジェクト 'dbo.Person' に重複したキーを挿入できません。 文は終了されました。

また、私は最近、2つの列に一意制約を適用するための解決策をブログに書きました。 のどちらかの順序で :

• 順序が重要でない一意の制約を強制する