unique index 와 null

아래 처럼 x 컬럼에 대해 unique 인덱스가 생성되어 있습니다.

하지만 null 경우는 중복해서 insert 가능합니다.

(Oracle/PostgreSQL/EPAS 모두 동일)

-----------------------------------------------------------------

SQL> create table x(x int);

SQL> create unique index xidx on x(x);

SQL> insert into x values(null);

SQL> insert into x values(null);

SQL> insert into x values(100);

SQL> select * from x;

         X
----------
        10

       100

SQL>

하지만 특이하게도,  MS-SQL 에서는 null 을 중복해서 insert 할수가 없습니다. null 또한 unique 한 값으로 보기 때문입니다.

만약 PostgreSQL 에서도 MS-SQL 처럼 동작하도록 하고자 한다면 다음과 같이 하면 됩니다.

SQL> create table x(x int);                                                        
CREATE TABLE
Time: 527.001 ms

SQL> CREATE UNIQUE INDEX xidx ON X ((X IS NULL)) WHERE X IS NULL; -- 이중괄호 주의!

CREATE INDEX
Time: 106.574 ms

SQL> insert into x values(100);
INSERT 0 1
Time: 3.136 ms

SQL> insert into x values(null);
INSERT 0 1
Time: 2.257 ms

SQL> insert into x values(null);
ERROR:  duplicate key value violates unique constraint "xidx"
DETAIL:  Key ((x IS NULL))=(t) already exists.
Time: 0.596 ms

SQL>