Not null contraints

"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

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 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 |/

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)