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?

Jacklighter

#13043497 – Centromere

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.

Meningitis

This does not work for a unique index over two or more columns though. e.g. create table x1 (c1 integer, c2 integer, unique (c1, c2)); then insert into x1 values (1, null); can only be done once. – Muriel

@a_horse_with_no_name - Correct! I was just answering the OP's question (as asked), where he mentioned "unique constraint column" (notice the singular, and no mention of a composite unique constraint). – Meningitis

thanks @a_horse_with_no_name ...it was really helpful. – Jacklighter

@ShubhamAnand - If you care about the rule for COMPOSITE unique constraints, the answer is that you CAN have multiple rows with nulls without violating the unique constraint. However, the correct concept of "null values" in the unique key is that ALL columns are null, not some null and the other(s) non-null. In a_horse's example, insert into x1 values (null, null) can be done ten times without violating the unique constraint. Sorry, I didn't know your situation was different from what you had in the question or I would have put that in my answer. – Meningitis

@a_horse_with_no_name - I will add this to my Answer since it seems to cause confusion. – Meningitis

Thanks a ton, the updated answer is very precise and helped me a lot. I am a newbie in Oracle SQL, btw. – Veinstone

Is this Oracle SQL? Oracle allows multiple nulls whereas MS SQL Server allows only 1 null in a unique key. – Kristlekristo

Answer 2

Note that in SQL Server only 1 null value is allowed in a unique column:

create table t1 (col1 int unique);
insert into t1 values (1);
insert into t1 values (null);
insert into t1 values (null);

Will result in ERROR.

Resolution