RE: columns of a unique index
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "Post to FreeList Oracle-L" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 14 May 2008 16:21:31 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Finn Jorgensen
Sent: Wednesday, May 14, 2008 4:03 PM
To: oxnard@xxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx> 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.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
