Re: Cannot insert '' into NOT NULL column

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:f6rj50ls9gf4gtvb262rlejbp59qeognfv_at_4ax.com...
> laredotornado_at_zipmail.com (D. Alvarado) wrote:
>
> >Hello,
> > I'm running Oracle 8.1.7 for Solaris. I have a situation where I
> >want my primary key to potentially include a '' value. However, it
> >appears Oracle treats '' the same as NULL, and of course, NULLs are
> >not allowed in primary keys. Is there anything I can do to allow the
> >oclumn to have a '' value AND index the column in some way? Or is the
> >only solution to hard code into my application that if inserting a '',
> >change it to something non-empty before the insert occurs?
> >
> >Thanks - Dave
>
> 2 Thoughts..
> Change the '' to ' ' - A blank string, not a NULL
>
> Or
>
> Instead of a Primary Key use a Unique Index ( it allows for NULLs)
> ( but, that cannot be used as the 'target' of a Foreign Key)

It can actually. I made a mistake when I said that it couldn't in a different thread.

My temperature is 101 at the moment, so that must be why I stuffed up so badly. ;-)

SQL> create table parent (
  2 col1 number,
  3 col2 varchar2(10));

Table created.

SQL> alter table parent add (constraint col1_uq unique(col1));

Table altered.

SQL> create table child (
  2 col1 number);

Table created.

SQL> alter table child add (constraint cpfk foreign key(col1) references parent (col1));

Table altered.

(This was 9i R2).

Regards
HJR Received on Thu Mar 18 2004 - 15:50:19 CST