RE: columns of a unique index

However remember that you cannot have multiple rows with one column value being null but the other column having existing values in the index:

  1 create unique index marktest_unique   2* on marktest ((fl1, fld2, fld4)

UT1 > l
  1* select fld1, fld2, fld4 from marktest UT1 > /

FLD1 FLD2 FLD4
---------- ---------- ----------

one                 1
TWO                 2 USER51
                    4 USER51

UT1 > insert into marktest (fld1, fld2, fld4)   2 values ('two',2,NULL);

1 row created.

UT1 > / -- just ran same insert again insert into marktest (fld1, fld2, fld4)
*
ERROR at line 1:
ORA-00001: unique constraint (MPOWEL01.MARKTEST_UNIQUE) violated

UT1 > insert into marktest (fld1, fld2, fld4) values (null,2,null);

1 row created.

UT1 > insert into marktest (fld1, fld2, fld4) values (null,2,null); insert into marktest (fld1, fld2, fld4) values (null,2,null) *
ERROR at line 1:
ORA-00001: unique constraint (MPOWEL01.MARKTEST_UNIQUE) violated

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Finn Jorgensen Sent: Wednesday, May 14, 2008 4:03 PM
To: oxnard_at_carolina.rr.com
Cc: Post to FreeList Oracle-L
Subject: Re: columns of a unique index

It is allowed to have nullable columns in a unique index. However, in a Primary Key constraint all the columns has to be NOT NULL.

A unique key could more accurately be referred to as an Alternate Key.

Finn

On 5/14/08, Oxnard Montalvo <oxnard_at_carolina.rr.com> wrote:
> Think it is ok to have them nullable? why or why not. Seem like in
general they act as another PK on a table.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l