Re: [GENERAL] Not null contraints
"Tamsin" [EMAIL PROTECTED] 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
Re: [GENERAL] Not null contraints
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 |/
Re: [GENERAL] Not null contraints
Philip Warner [EMAIL PROTECTED] 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