Thread: Modifying NOT NULL Constraint
In the following archived email: http://www.postgresql.org/mhonarc/pgsql-admin/2000-05/msg00025.html this was posed as a solutions to modifying the NOT NULL constraint: >update pg_attributes set attnotnull = 'f' where oid = oidofnotnullcolumn; >vacuum analyze; I didn't find any further comment on this so I decided to go right to the source... Is this recommended or not? Are there any side effects of which I should be aware before attempting to use this? If this is not a valid way to accomplish the modification of the NOT NULL constraint, then are there plans for an implementation of it (I enjoy the new ALTER COLUMN DEFAULT)? Thanks, -Dan Wilson phpPgAdmin Author http://www.phpwizard.net/phpPgAdmin Please reply to me directly as I'm not subscribed to the list.
"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> this was posed as a solutions to modifying the NOT NULL constraint:
>> update pg_attributes set attnotnull = 'f' where oid = oidofnotnullcolumn;
>> vacuum analyze;
attnotnull is where the gold is hidden, all right. The 'vacuum analyze'
step is mere mumbo-jumbo --- there's no need for that.
> Are there any side effects of which I should be aware before attempting to
> use this?
Changing in that direction should be safe enough. Turning attnotnull
*on* is a little more dubious, since it won't magically make any
existing null entries in the column go away. attnotnull just governs
the check that prevents you from storing new nulls.
regards, tom lane
"Dan Wilson" <dan_wilson@geocities.com> writes:
> So if I'm understanding this correctly, this would be able to remove the NOT
> NULL constraint, but would not be able to set the NOT NULL constraint. Is
> that correct?
Oh, you can set attnotnull if you feel like it. My point is just that
nothing much will happen to any existing null values in the column.
It's up to you to check for them first, if you care.
> If that is correct, are their plans to implement a post-create setting of
> the NOT NULL constraint?
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?
You can implement any of these behaviors for yourself with a couple of
SQL commands inside a transaction, so I'm not sure that I see the need
to have a neatly-wrapped-up ALTER TABLE command that will only do one
of the things you might want it to do.
regards, tom lane
Ok... point taken! I guess the masters always have reasons for why things aren't implemented. -Dan ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Dan Wilson" <dan_wilson@geocities.com> Cc: <pgsql-hackers@postgresql.org> Sent: Wednesday, June 14, 2000 12:31 AM Subject: Re: [HACKERS] Modifying NOT NULL Constraint > "Dan Wilson" <dan_wilson@geocities.com> writes: > > So if I'm understanding this correctly, this would be able to remove the NOT > > NULL constraint, but would not be able to set the NOT NULL constraint. Is > > that correct? > > Oh, you can set attnotnull if you feel like it. My point is just that > nothing much will happen to any existing null values in the column. > It's up to you to check for them first, if you care. > > > If that is correct, are their plans to implement a post-create setting of > > the NOT NULL constraint? > > 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? > > You can implement any of these behaviors for yourself with a couple of > SQL commands inside a transaction, so I'm not sure that I see the need > to have a neatly-wrapped-up ALTER TABLE command that will only do one > of the things you might want it to do. > > regards, tom lane
Tom Lane 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? I would vote for refuse the command. It enforces the integrity of the data. You can always do an appropriate update command first if you think there are nulls in there.
So if I'm understanding this correctly, this would be able to remove the NOT NULL constraint, but would not be able to set the NOT NULL constraint. Is that correct? If that is correct, are their plans to implement a post-create setting of the NOT NULL constraint? -Dan ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Dan Wilson" <phpPgAdmin@acucore.com> Cc: <pgsql-hackers@postgresql.org> Sent: Tuesday, June 13, 2000 10:33 PM Subject: Re: [HACKERS] Modifying NOT NULL Constraint > "Dan Wilson" <phpPgAdmin@acucore.com> writes: > > this was posed as a solutions to modifying the NOT NULL constraint: > >> update pg_attributes set attnotnull = 'f' where oid = oidofnotnullcolumn; > >> vacuum analyze; > > attnotnull is where the gold is hidden, all right. The 'vacuum analyze' > step is mere mumbo-jumbo --- there's no need for that. > > > Are there any side effects of which I should be aware before attempting to > > use this? > > Changing in that direction should be safe enough. Turning attnotnull > *on* is a little more dubious, since it won't magically make any > existing null entries in the column go away. attnotnull just governs > the check that prevents you from storing new nulls. > > regards, tom lane
> 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). > You can implement any of these behaviors for yourself with a couple of > SQL commands inside a transaction, so I'm not sure that I see the need > to have a neatly-wrapped-up ALTER TABLE command that will only do one > of the things you might want it to do. True, but it would be nice to be able to add a check constraint later, and as long as you're doing it, it seems silly to ignore NOT NULL.
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@Yahoo.com #
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.