☰

2つの列の組み合わせに一意の制約を追加する 質問する

tag-icon • tag-icon sql sql-server
2つの列の組み合わせに一意の制約を追加する 質問する

テーブルがあるのですが、どういうわけか、同じ人がPersonテーブルに 2 回アクセスしてしまいました。現在、主キーは単なる自動番号ですが、他に 2 つのフィールドがあり、それらを強制的に一意にしたいと考えています。

たとえば、フィールドは次のとおりです。

ID  
Name  
Active  
PersonNumber

一意の PersonNumber と Active = 1 を持つレコードを 1 つだけ必要とします。
(したがって、2 つのフィールドの組み合わせは一意である必要があります)

SQL サーバーの既存のテーブルで、他のユーザーが既存の値と同じ値を挿入した場合に失敗するようにして、アプリケーション コードでこれを心配しないようにする最善の方法は何ですか。

ベストアンサー1

重複を削除したら、次の操作を行います。

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

または

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

もちろん、SQL Server に行の挿入を試みさせて例外を返す前に、まずこの違反をチェックする方がよい場合がよくあります (例外はコストがかかります)。

アプリケーションに変更を加えずに、例外がアプリケーションにバブルアップするのを防ぐには、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

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

編集: ここに、質問と同じ名前を使用しても、まさにあなたが求めていることを実行し、それを証明する例があります。上記のアイデアが組み合わせではなく、どちらか一方の列のみを扱うと仮定する前に、試してみる必要があります...

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

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

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

また、最近、2 つの列に任意の順序で一意の制約を適用するソリューションについてブログに書きました。

おすすめ記事