SQLでの複数列ユニークインデクスにおけるNULLの挙動について

SQLでユニーク制約を使用するとき、ANSI SQLではNULLを除いた値に対してユニーク制約がかかり、SQL ServerではNULLを含めてユニーク制約がかかることが知られています。この記事では、ユニーク制約のついた複数列インデクスにおけるNULLの挙動について見ていきます。

PostgreSQL

環境: PostgreSQL 12

CREATE TABLE table_two_col (
  col1 int, -- Nullable
  col2 int  -- Nullable
);
CREATE UNIQUE INDEX Idx_table_two_col_col1_col2 ON table_two_col (col1, col2);

INSERT INTO table_two_col (col1, col2) VALUES (1, 1);
INSERT INTO table_two_col (col1, col2) VALUES (1, 1); -- error
INSERT INTO table_two_col (col1, col2) VALUES (1, NULL);
INSERT INTO table_two_col (col1, col2) VALUES (1, NULL); -- OK
INSERT INTO table_two_col (col1, col2) VALUES (NULL, NULL);
INSERT INTO table_two_col (col1, col2) VALUES (NULL, NULL); -- OK

SELECT * FROM table_two_col;
-- col1,col2
-- 1,1
-- 1,NULL
-- 1,NULL
-- NULL,NULL
-- NULL,NULL

SQL Server

環境: SQL Server 2017

CREATE TABLE table_two_col (
  col1 int, -- Nullable
  col2 int -- Nullable
);
CREATE UNIQUE INDEX Idx_table_two_col_col1_col2 ON table_two_col (col1, col2);

INSERT INTO table_two_col (col1, col2) VALUES (1, 1);
INSERT INTO table_two_col (col1, col2) VALUES (1, 1); -- error
INSERT INTO table_two_col (col1, col2) VALUES (1, NULL);
INSERT INTO table_two_col (col1, col2) VALUES (1, NULL); -- error
INSERT INTO table_two_col (col1, col2) VALUES (NULL, NULL);
INSERT INTO table_two_col (col1, col2) VALUES (NULL, NULL); -- error

SELECT * FROM table_two_col;
-- col1,col2
-- NULL,NULL
-- 1,NULL
-- 1,1

SQL Serverで、Filtered IndexによりANSI SQLの動作を再現する場合

Filtered Index(フィルター選択されたインデックス)を使用することにより、ANSI SQLのユニークインデックスのNULLの挙動を再現する方法がよく使用されています。

CREATE TABLE table_two_col (
  col1 int, -- Nullable
  col2 int -- Nullable
);
CREATE UNIQUE INDEX Idx_table_two_col_col1_col2 ON table_two_col (col1, col2) WHERE col1 IS NOT NULL AND col2 IS NOT NULL; -- この行のWHERE以降の部分

INSERT INTO table_two_col (col1, col2) VALUES (1, 1);
INSERT INTO table_two_col (col1, col2) VALUES (1, 1); -- error
INSERT INTO table_two_col (col1, col2) VALUES (1, NULL);
INSERT INTO table_two_col (col1, col2) VALUES (1, NULL); -- OK
INSERT INTO table_two_col (col1, col2) VALUES (NULL, NULL);
INSERT INTO table_two_col (col1, col2) VALUES (NULL, NULL); -- OK

SELECT * FROM table_two_col;
-- col1,col2
-- 1,1
-- 1,NULL
-- 1,NULL
-- NULL,NULL
-- NULL,NULL