Re: conditional constraints

Dennis Gearon wrote:
>
> Also, (I would love to be corrected nicely if I'm wrong), a column can be
> NULL'able and have a foreign contraint on it, and have NULL values, right?

Right!

That's exactly where the wrong assumption was. A FOREIGN KEY does not
require a NOT NULL (a PRIMARY KEY implies that). Foreign key columns
filled with NULLs are absolutely valid and are not considered a breach
of integrity.


Jan

>
> Jan Wieck wrote:
> > tom dyson wrote:
> >
> >>(on behalf of my colleague, Neal Todd)
> >>
> >>This question is about whether it's possible to have conditionality on a
> >>constraint, or rather (presuming it's not possible), how it can be emulated
> >>perhaps with a trigger.
> >>
> >>The scenario is this (but is fairly general anyway)...
> >>
> >>Table "P" storing projects with a project id primary key.
> >>and
> >>Table "D" storing diary entries relating to projects with foreign key
> >>constraint referencing project ids in table "P".
> >>
> >>Fine so far, we have referential integrity on the project ids in table "D".
> >>
> >>However, we need to add diary entries that are for a generic "non-project"
> >>category. Without the constraint we could just have a null or dummy (e.g. 0)
> >>entry in D's project id foreign key. But with the constraint the referential
> >>integrity is broken.
> >
> >
> > Your assumption is wrong. That's the strange thing about NULL values, we
> > can't tell what they are made of or where they are coming from, but we
> > certainly know what they are good for :-)
> >
> >
> > Jan
> >


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #