Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: unique constraint and nulls
On Thu, 25 May 2006 23:44:13 +0200, Chris Seidel wrote:
> Hallo,
>
> I need a unique constraint over 2 columns. OK, no problem.
>
> But it must ignore rows where column 1 has a null value.
>
> Is this possible with a constraint? Or do I have to use a trigger?
>
> Thank you.
At the risk of being treated the same as the guy who suggested counting the teeth in horse's mouth to the group of philosophers debating how many teeth does a horse have, here is your answer:
1 create table a(
2 col1 number,
3* col2 varchar2(5))
SQL> /
Table created.
SQL> alter table a add constraint a_uk unique(col1,col2);
Table altered.
SQL> insert into a values(null,null);
1 row created.
SQL> insert into a values(null,'Test');
1 row created.
SQL> insert into a values(1,null);
1 row created.
SQL> /
insert into a values(1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.A_UK) violated
SQL> insert into a values(null,null);
1 row created.
SQL> commit;
Commit complete.
SQL> SQL> select count(*) from a;
COUNT(*)
4
-- http://www.mgogala.comReceived on Fri May 26 2006 - 00:35:07 CDT