Re: UNIQUE and NULL in SQL

"Brian Smith" <brian-l-smith_at_uiowa.edu> wrote in message news:60360d48.0201151907.6ce6fa88_at_posting.google.com...
> gstrader_at_yahoo.com (Gary Strader) wrote in message
news:<681fb18d.0201151240.4f9e267c_at_posting.google.com>...
> > brian-l-smith_at_uiowa.edu (Brian Smith) wrote in message
news:<60360d48.0112291928.2e7c5818_at_posting.google.com>...
> > > I want the fourth insert statement above to succeed while still
> > > enforcing the UNIQUE constraint for non-null columns. Is that
> > > possible?
> >
> > Yes - write an insert trigger to check it. And ignore the
> > admonishments as to whether it's "correct" to do so since you probably
> > don't have the luxury to theorize about it.
>
> I was hoping for a solution that would work on both PostgreSQL and
> Oracle. Oracle won't let a person create an insert trigger that issues
> a SELECT statement on the table to which the trigger belongs.
That is the so called mutating table problem. Look in the application developers guide
for solutions. (In essence it boils down to making row triggers that store the affected rows, and
an after statement trigger that issues the select, and checks. It would not be possible to code such thing in a portable manner as far as I know.

But, anyway, such questions are better asked in the Oracle and PostgreSQL groups, comp.database.theory is not for questions about two or more database systems.

Willem Dekker

>
> Thanks anyway,
> Brian
Received on Wed Jan 16 2002 - 15:22:58 CET