Thread: 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)