Discussion Locked

oracle

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How many null values can be insert in a table if there should be unique key?

2 replies

by gu.pipperr

Testcase:
---
create table t (id number, text varchar2(10));
alter table t add(UNIQUE (text));

---
insert into t (id) values (10);
insert into t (id) values (11);
insert into t (id) values (12);

------
select id,nvl(text,'NULL') from t;
ID NVL(TEXT,'
-------- ----------
10 NULL
11 NULL
12 NULL

---------
insert into t (id,text) values (13,'A');
insert into t (id,text) values (14,'A');

unique constraint (SYS_C0043147) violated

----------

Cause:

Null has no value, if the column is not null you can have null's in the table

----
Solution

set not Null contraint to the column
and set all Null values on a real value.

alter table t modify (text not null);

by Former member

The word "unique" is defined as ... ?

This discussion is closed to new replies.