SQL Serverでも論理削除と一意性制約を両立

一意性(UNIQUE)制約はNULLでない値に対してだけ効く、そう思っていた頃が私にもありました…
SQL ServerはNULLも一意性制約の対象になります。NULLになっている行が複数あると重複だと判定されます。SQL-92の規格書をあたってみると、これは完全な規格違反なんですけどね。

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

つまりSQL Serverじゃ以前解説した

• 有効なレコードだけに一意性制約をかける

が使えないってことです。

でも大丈夫。
SQL Serverはインデックスフィルタで他製品のUNIQUE制約と同じ振る舞いを実現できます。
インデックスフィルタ。要は、テーブルのうち条件に合う行だけにインデックスを張る機能です。フィルタ条件をIS NOT NULLにして、それをUNIQUEインデックスにしてしまえば、ほら、NULLを無視するUNIQUE制約そのもの。

例のページのテーブル定義に当てはめれば、

CREATE UNIQUE NONCLUSTERED INDEX NAME_IS_UNIQUE_IF_ENABLED
ON usres (name, enabled)
WHERE enabled IS NOT NULL;

と書けます。

でも、インデックスフィルタの機能性を活かせばこんな冗長な定義する必要ありませんね。

CREATE TABLE usres (
    id bigserial NOT NULL PRIMARY KEY,
    name character varying(40) NOT NULL,
    enabled bit NOT NULL,
);

CREATE UNIQUE NONCLUSTERED INDEX NAME_IS_UNIQUE_IF_ENABLED
ON usres (name)
WHERE enabled = 1;

むしろ、設計意図をそのまま制約定義に書き下ろせています。

インデックスフィルタはSQL標準では部分インデックスと呼ばれており、SQL server以外ではPostgreSQLで使えます。