Re: Modifying NOT NULL Constraint

Well, I wasn't planning on doing it any time soon... I just wanted to
mention it for
completeness-sake since it was my code that does it "wrong" and I'd rather
mention
it than have someone come back to me asking me why my code does what it
does.
The basic point is that ALTER TABLE isn't too much of a difference from
normal
constraint checking...  If the constraint fails when the ALTER TABLE is done
the
statement should abort just like any other statement that causes a
constraint failure.

> Stephan Szabo wrote:
> > > What do you think should happen if there are null values?  Refuse the
> > > command?  Delete the non-compliant rows?  Allow the rows to remain
> > > even though the column is now nominally NOT NULL?
> >
> > With ALTER TABLE ADD CONSTRAINT on a non-deferrable NOT
> > NULL it should fail.  At the end of statement the constraint is not
> > satified,
> > an exception is raised and the statement is effectively ignored. It's
alot
> > more complicated for deferrable constraints, and I didn't even actually
> > take that into account when I did the foreign key one (because I just
> > thought
> > of it now).
>
>     Forget it!
>
>     Doing
>
>         BEGIN;
>         ALTER TABLE tab ADD CONSTRAINT ... INITIALLY DEFERRED;
>         UPDATE tab SET ... WHERE ... ISNULL;
>         COMMIT;
>
>     is  totally pathetic. Do it the other way round and the ALTER
>     TABLE is happy.  As Tom usually says "if it hurts,  don't  do
>     it". We have more important problems to spend our time for.