Обсуждение: Not null contraints

Quick question -
I want to remove a not null constraint from a table.  I've read the posts
about having to rename, recreate without not nulls & insert back into the
table.  This is fine, but the table also has a lot of foreign keys/rules etc
& recreating all these is a bit of pain.
I tried updating pg_attribute & setting attnotnull to 'f' for the field in
question.  This seems to have worked.  Is it safe?! - is there anything else
I should be aware of?

Thanks,
Tamsin

"Tamsin" <tg_mail@bryncadfan.co.uk> writes:
> I want to remove a not null constraint from a table.  I've read the posts
> about having to rename, recreate without not nulls & insert back into the
> table.  This is fine, but the table also has a lot of foreign keys/rules etc
> & recreating all these is a bit of pain.
> I tried updating pg_attribute & setting attnotnull to 'f' for the field in
> question.  This seems to have worked.  Is it safe?! - is there anything else
> I should be aware of?

Yup, that should do the trick.  Not much magic here...

            regards, tom lane

At 00:26 14/10/00 -0400, Tom Lane wrote:
>> I tried updating pg_attribute & setting attnotnull to 'f' for the field in
>> question.  This seems to have worked.  Is it safe?! - is there anything
else
>> I should be aware of?
>
>Yup, that should do the trick.  Not much magic here...
>

Just to confirm - does this mean we have ALTER TABLE ADD CONSTRAINT, but
not ALTER TABLE DROP CONSTRAINT. If so, should it go on a list somewhere?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
> At 00:26 14/10/00 -0400, Tom Lane wrote:
>>> I tried updating pg_attribute & setting attnotnull to 'f' for the field in
>>> question.  This seems to have worked.  Is it safe?! - is there anything
>>> else I should be aware of?
>>
>> Yup, that should do the trick.  Not much magic here...

> Just to confirm - does this mean we have ALTER TABLE ADD CONSTRAINT, but
> not ALTER TABLE DROP CONSTRAINT. If so, should it go on a list somewhere?

No, it just means that NOT NULL constraint is handled via a special
flag attached to the column's pg_attribute entry.  More general
constraints are handled with other catalog entries.  (I think this
is largely a historical artifact, not necessarily a good idea.)

Another relevant comment is that *removing* a NOT NULL constraint
doesn't pose any risk of creating invalid entries in the table data.
So there's no need to worry about cross-checking.

            regards, tom lane

At 02:02 14/10/00 -0400, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> At 00:26 14/10/00 -0400, Tom Lane wrote:
>>>> I tried updating pg_attribute & setting attnotnull to 'f' for the
field in
>>>> question.  This seems to have worked.  Is it safe?! - is there anything
>>>> else I should be aware of?
>>>
>>> Yup, that should do the trick.  Not much magic here...
>
>> Just to confirm - does this mean we have ALTER TABLE ADD CONSTRAINT, but
>> not ALTER TABLE DROP CONSTRAINT. If so, should it go on a list somewhere?
>
>No, it just means that NOT NULL constraint is handled via a special
>flag attached to the column's pg_attribute entry.

So there is no 'ALTER TABLE ALTER <field> ALLOW NULLS' or whatever.

>Another relevant comment is that *removing* a NOT NULL constraint
>doesn't pose any risk of creating invalid entries in the table data.
>So there's no need to worry about cross-checking.

This should apply to removing *any* constraint AFAICT...

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

On Sat, 14 Oct 2000, Philip Warner wrote:

> At 02:02 14/10/00 -0400, Tom Lane wrote:
> >>> Yup, that should do the trick.  Not much magic here...
> >
> >> Just to confirm - does this mean we have ALTER TABLE ADD CONSTRAINT, but
> >> not ALTER TABLE DROP CONSTRAINT. If so, should it go on a list somewhere?
> >
> >No, it just means that NOT NULL constraint is handled via a special
> >flag attached to the column's pg_attribute entry.
>
> So there is no 'ALTER TABLE ALTER <field> ALLOW NULLS' or whatever.

I wonder how you actually are supposed to add and remove NOT NULL
constraints.  ALTER TABLE ADD/DROP constraint work on table constraints,
and I don't think NOT NULL is among them, and I don't actually see
anything in spec beyond changing defaults and dropping for existing
columns.

>
> >Another relevant comment is that *removing* a NOT NULL constraint
> >doesn't pose any risk of creating invalid entries in the table data.
> >So there's no need to worry about cross-checking.
>
> This should apply to removing *any* constraint AFAICT...

True, but there might be cases in which removing a constraint invalidates
another one (removing the unique constraint that a foreign key constraint
references - not that we do anything about this yet)