Re: select * from mytable where myfield=null;

Ofcourse Noel! How would you design the following example:

Table ORDER:
------------
order_id (PK) not null
date not null
...
invoice_id (FK) null

Table INVOICE:
--------------
invoice_id (PK) not null
...

Column invoice_id in the ORDER table can not be defined as "not null" because we don't know invoice_id yet and there can not be "dummy" value because of FK constraint. This is 1:N (or 1:1) relationship where the other object is not mandatory.

_________ _______
| | | |
| INVOICE |o----------o<| ORDER |
|_________| |_______|


Hoanui


--- On Tue 05/14, Noel Yap wrote:
> Is it good practice to use NULL values for foreign key
> fields?
>
> Thanks,
> Noel
> --- Hoanui Morangie wrote:
> >
> > I need NULL value, some of these columns are foreign
> > keys. And using dummy values is not good practice
> > anyway.
> >
> > Hoanui
> >
> >
> >
> > --- On Tue 05/14, Marin Dimitrov wrote:
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px;
> > MARGIN-LEFT: 5px;
> > > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT:
> > 0px">
> > > ----- Original Message -----
> > > style="BACKGROUND: #e4e4e4; FONT: 10pt arial;
> > font-color:
> > > black">From:
> > > Hoanui
> > > Morangie
> > >
> > >
> > > Hi, I understand that this behavior is a
> > standard but agree
> > > with
> > > Bruno that it's nonsense. I have trouble with it
> > in my own application.
> > > I use
> > > sql command like "select * from A where A.a=?
> > and A.b=? and A.c=? and
> > > A.d=?
> > > and A.e=?". There are five parameters in my
> > query and each of them can
> > > be
> > > null! I can not create SQL command string for
> > all possible combinations
> > > of
> > > null values so I have to build the query
> > dynamically. Why then use
> > > prepared
> > > statement? I doubt that anybody ever needed
> > x=null in SQL
> > > command but
> > > it seems that my example is very common. There
> > SHOULD be some workaround
> > > for
> > > that. But I agree that this is not topic for
> > this list.
> > >
> > >
> > >
> > > don't use NULLs - introduce a dummy value
> > > that bears the
> > > semantic of NULL so u can use "= XXX" instead of
> "
> > is
> > > NULL"
> > >
> > > hth,
> > >
> > > Marin
> > >
> > > ----"...what you
> > > brought
> > > from your past, is of no use in your present. When
> > you must choose a
> > > new
> > > path, do not bring old experiences with you. Those
> > who strike out
> > > afresh,
> > > but who attempt to retain a little of the old
> > life, end up torn apart
> > > by
> > > their own memories. "
> > >
> > >
> >
> > ------------------------------------------------
> > Join Excite! - http://www.excite.com
> > The most personalized portal on the Web!
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org
> > so that your
> > message can get through to the mailing list cleanly
>
>
> __________________________________________________
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
>

---

Join Excite! - http://www.excite.com
The most personalized portal on the Web!