Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: unique constraint and nulls

Re: unique constraint and nulls

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Fri, 26 May 2006 05:35:07 GMT
Message-Id: <pan.2006.05.26.05.35.07.514162@sbcglobal.net>


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.com
Received on Fri May 26 2006 - 00:35:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US