Azure Synapse Analytics （Synapse ワークスペース） プライマリキーとユニーク制約の話

0. 注意点

2022年3月時点の情報です。最新情報は別途ご確認ください。

1.1 プライマリキーとユニーク制約の制限

専用 SQL プールでは、次のテーブル制約がサポートされています。
・PRIMARY KEY は、NONCLUSTERED と NOT ENFORCED が両方とも使用されている場合にのみサポートされます。
・UNIQUE 制約は、NOT ENFORCED が使用されている場合にのみサポートされます。

[参考資料]
Azure Synapse Analytics で専用 SQL プールを使用する主キー、外部キー、および一意キー（公式ドキュメント）

1.2 補足

NOT ENFORCEDはこれらの制約を強制しないオプション
→つまりプライマリキーとユニーク制約で、重複レコードのインサートによるエラーは発生しない

Azure Synapse Analyticsの専用SQL プールでデータの一意制を確保するためには、アプリケーション側のロジックで管理をする必要がある

2.1 プライマリキーとユニーク制約を使用する目的

解説
主キーや一意キーを使用すると、専用 SQL プール エンジンでクエリの最適な実行プランを生成できます。 主キー列または一意制約列のすべての値は、一意である必要があります。
専用 SQL プールで主キーまたは一意制約を使用してテーブルを作成した後、ユーザーはそれらの列のすべての値が一意であることを確認する必要があります。 これに違反すると、クエリで不正確な結果が返される可能性があります。 この例では、主キーまたは一意制約の列に重複した値が含まれている場合に、クエリでどのように不正確な結果が返される可能性があるかを示します。

[参考資料]
Azure Synapse Analytics で専用 SQL プールを使用する主キー、外部キー、および一意キー（公式ドキュメント）

2.2 補足

プライマリキーとユニーク制約を使用することで、これが設定された列に重複する値が存在しないと専用 SQL プール エンジンに判定される
これによって生成されるクエリの実行プランに影響が発生する

例
ユニーク制約が設定されたa1列には重複する値が存在しないはずなので集約処理を省略する

2.3 検証

[参考資料]
Azure Synapse Analytics で専用 SQL プールを使用する主キー、外部キー、および一意キー（公式ドキュメント）

2.3.1 プライマリキーとユニーク制約なしの実行プラン

以下は、検証用テーブル(t1)に集約処理を実行した際の実行プラン

検証用テーブル(t1)の作成

-- Create table t1
CREATE TABLE t1 (a1 INT NOT NULL, b1 INT) WITH (DISTRIBUTION = ROUND_ROBIN)
-- Insert values to table t1 with duplicate values in column a1.
INSERT INTO t1 VALUES (1, 100)
INSERT INTO t1 VALUES (1, 1000)
INSERT INTO t1 VALUES (2, 200)
INSERT INTO t1 VALUES (3, 300)
INSERT INTO t1 VALUES (4, 400)

集約処理

SELECT a1, COUNT(*) AS total FROM t1 GROUP BY a1

2.3.2 プライマリキーとユニーク制約ありの実行プラン

検証用テーブルのa1列ににユニーク制約を設定したうえで再度集約処理を実行すると実行プランから集約処理が消える
これはa1列にユニーク制約が設定されていることから、a1列に重複する値はない前提で実行プランの作成が行われているから

検証用テーブル(t1) a1列へのユニーク制約の設定

ALTER TABLE t1 ADD CONSTRAINT unique_t1_a1 unique (a1) NOT ENFORCED

集約処理

SELECT a1, COUNT(*) AS total FROM t1 GROUP BY a1

3. 所感

直感的にはデータの一意制を確保するために使えないユニーク制約って何に使うのって感じでしたが、調べてみるとちゃんと用途についてもドキュメントにまとめられてますね

記事の内容について何かあればコメントにご記載ください