can a unique constraint column have 2 or more null values? (oracle)

Question

Is it possible to have 2 or more null values in unique constraint column?

Answer 1

Easy to check: (The answer is YES)

create table t1 (col1 number unique);

Table T1 created.

insert into t1 values (1);

1 row inserted.

insert into t1 values (null);

1 row inserted.

insert into t1 values (null);

1 row inserted.

select rownum, col1 from t1;

    ROWNUM       COL1
---------- ----------
         1          1
         2           
         3           

3 rows selected.

Edit: While what I show above is the answer when only one column is involved in a unique constraint, one can also have composite unique keys (constraints defined at the table level, rather than column level - involving two or more columns). In that case, if say the unique key is on (col1, col2, col3), then (1, 1, 0) and (1, 1, 3) are not duplicates, because they aren't identical in every position. In this case, (1, 1, null) is allowed, but only once. The correct "generalization" of null "value" in a column, however, is for ALL values in ALL THREE columns to be null. In that regard, rows with "null values" in the unique key columns are still allowed any number of times.

That is: While (1, 1, null) is allowed, but not more than once, a row with values (null, null, null) in the three columns that make up the unique key are allowed any number of times - just like in the single-column case.