Re: Modifying NOT NULL Constraint

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.

Jan

BTW: Still have your other FK related mail to process. Will do so soon.

--

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