21

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

Shubham Anand
  • 551
  • 2
  • 7
  • 19
  • http://stackoverflow.com/questions/13042997/how-to-create-unique-index-on-fields-with-possible-null-values-oracle-11g – Ryan27 Oct 13 '16 at 17:12

1 Answers1

40

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.

  • 3
    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. –  Oct 13 '16 at 19:35
  • 1
    @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). –  Oct 13 '16 at 19:39
  • thanks @a_horse_with_no_name ...it was really helpful. – Shubham Anand Oct 15 '16 at 19:36
  • 2
    @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. –  Oct 15 '16 at 20:03
  • 1
    @a_horse_with_no_name - I will add this to my Answer since it seems to cause confusion. –  Oct 15 '16 at 20:04
  • Thanks a ton, the updated answer is very precise and helped me a lot. I am a newbie in Oracle SQL, btw. – shripal mehta Apr 29 '23 at 11:01